Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Index Match w/Offset Formula Help

0

Hello!

**Update*** - File Attached.

The way my data spits out I do not have a straight horizontal line from my match point to the data that needs to be retrieved. Meaning: My match will be in A3, but the data I need to retrieve is in G7.  The data I need to retrieve will always be 4 rows down and 7 columns across from the match point (cell).

See link:

https://docs.google.com/spreadsheets/d/1g7SXXTfTfaTWSTNQS8bjS3QwR81SmA5QzcPs8NKWkNc/edit?usp=sharing

Answer
Discuss

Answers

0

The problem in your formula is caused by illogical designation of ranges. The range $G$10:$G$109 starts in row 10. Its first row is row 10. The range A$6:$A$100 has its first row in row 6.

The MATCH function returns a number, the nth row in A$6:$A$100. The criterum 3960 will be found in the range's first row. The MATCH function returns a 1.

The INDEX function applies this number to the range $G$10:$G$109 . The first row of $G$10:$G$109 is in row 10. In the formula below the range is altered to start on the same row.

[C23] =INDEX($G$6:$G$109,MATCH(B23,$A$6:$A$100,0)+4)

The MATCH function still returns a 1 but that isn't the cell you want. You want a row which is 4 rows below the one found by MATCH. Therefore the above formula adds +4. 1 + 4 = 5, and that is the cell from which the correct result is retrieved.

Your solution achieves the same result with fewer keystrokes. Some people would call it more elegant because of that which I suggest to weigh against transparency, which counts when reviewing the formula. The question that remains unanswered for me is, what is your question.

You seem to have thought of using OFFSET. By both above measures such a solution wouldn't be considered superior. For argument's sake, here it is.

=OFFSET(INDEX($G$6:$G$109,MATCH(B23,$A$6:$A$100,0)),4,0)
Discuss

Discussion

I really wish I knew how to uplpad a file to this site. There is no error in my ranges, my data spits out this way and I need to offset my index match formula in order to retrieve cell data that is located not on an A row but on a G row. Since I cannot for the life of me figure out how to upload on this site, I will look elsewhere for an answer.
Nkvet Aug 5, '18 at 8:22 pm
Variatus explained how in his answer. Click "Edit" under your question and scroll down and click the very large button at the bottom of the page that says "Add Files to the Post" and then add your file. The button is directly on top of the "Cancel" button.
don (rep: 1989) Aug 6, '18 at 8:35 am
Thank you, I have uploaded my file.
Nkvet Aug 6, '18 at 9:58 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login