Use Excel Match Result as Column Selection

Question

I have a MATCH expression that returns the valid row number.

I now need to combine this result with a known Column identifier to return the results of that cell.

So, if something on A50 = "apple", then I can get the contents of cell D50.

I looked at INDIRECT and INDEX, but I'm not seeing how it can help.

Answer:

=INDEX('SHEET1'!A:D,MATCH(SHEET2!A2,'SHEET1'!B:B,0),4)

I got INDEX to work. It took some more reading up on it.

'SHEET1'!A:D is the range for INDEX to work with.

MATCH(SHEET2!A2,'SHEET1'!B:B,0) is pulling the row based upon my MATCH criteria.

4 is the column to return the cell contents from using the row number from the MATCH above.

Hopefully this will help someone else understand how to use INDEX.

Solution 1

=INDEX('SHEET1'!A:D,MATCH(SHEET2!A2,'SHEET1'!B:B,0),4)

I got INDEX to work. Took some more reading up on it.

'SHEET1'!A:D is the range for INDEX to work with. MATCH(SHEET2!A2,'SHEET1'!B:B,0) is pulling the row based upon my MATCH criteria. 4 is the COLUMN to return the cell contents from using the ROW number from the MATCH above.

However, the other options given were very helpful as well.

Solution 2

Give VLOOKUP a try. For example,

=VLOOKUP("apple",$A$1:$D$100,4,false)

It's a very useful function.

Solution 3

INDIRECT allows you to refer to any arbitrary cell in the sheet by specifying its location using a dynamic value. In your case, you'll want to do something like this:

=INDIRECT("D"&MATCH(<your match here>))

That will return the value of the cell D50 in the example you've given. The Excel documentation says it returns a "reference" to that cell, but in reality it's immediately evaluated to the cell's value.

The main benefit of this approach over VLOOKUP is that INDIRECT will refer to any arbitrary cell, whereas VLOOKUP requires a known data range and a matching value. For example, if your MATCH criteria references another sheet from the data you want to pull, your best option is INDIRECT.

Comments

[Admin]

There are a few reasons why INDEX/MATCH can be better than VLOOKUP. INDEX/MATCH is quicker over very large ranges; doesn't require the lookup field to be before the return field and doesn't have ascending order problems that VLOOKUP sometime encounters.