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.

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