5

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.

Grant Miller
  • 27,532
  • 16
  • 147
  • 165
Shawn
  • 331
  • 2
  • 3
  • 16
  • 1
    Is there a particular reason that you don't wish to use VLOOKUP()? It seems that your data is organized in a tabular format. – Jubbles Apr 22 '11 at 17:01
  • I figured out how to get index to work. I'd not run across vlookup before, but it does look like it would have worked. – Shawn Apr 22 '11 at 17:02
  • You should post your answer as an answer, not an edit to your question. Then you should accept your own answer to flag this question as solved. – Jean-François Corbett Apr 22 '11 at 19:10

3 Answers3

4

=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.

Shawn
  • 331
  • 2
  • 3
  • 16
3

Give VLOOKUP a try. For example,

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

It's a very useful function.

Jubbles
  • 4,450
  • 8
  • 35
  • 47
  • 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. –  Apr 22 '11 at 18:18
2

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.

Karelzarath
  • 287
  • 1
  • 11