# LOOKUP, INDEX, MATCH, MAX?

I have been tasked with seeing if I can pull column header info into a report. The report uses a series of lookups to pull information from two different sources.   The lookup value is in column B,  table array is B:O. My challenges is that I need to look at columns j-o by row and find the column with the last value and return the column header value, is this possible?
Sample-Level.xlsx
NBVC

Try:

=IFERROR(INDEX(\$C\$2:\$H\$2,MATCH(10^10,\$C3:\$H3)),0)

copied down.

jmac001

I apologize for not being clear the information is located in a different workbook. Is it still possible to do with Index/Match?
e.g

=IFERROR(INDEX('[Sample-Level.xlsx]Sheet1'!\$C\$2:\$H\$2,MATCH(10^10,'[Sample-Level.xlsx]Sheet1'!\$C3:\$H3)),0)

Can you please explain what 10^10 does.  Thanks
It's a very large number. Match() without the third argument looks for the largest match that is smaller than the, LOOKUP value which you should 10^10.  so the last number that is smaller in each row is the last number in the row always.

Thanks, question how does it match back and return the data to the appropriate cell if a lookup is not being used.  The forumla is working but the data is not aligned to the correct project?
So you are saying you want to match up the store too, which may not be in same order?

If so,

=IFERROR(INDEX('[Sample-Level.xlsx]Sheet1'!\$C\$1:\$H\$1,MATCH(10^10,INDEX('[Sample-Level.xlsx]Sheet1'!\$C\$2:\$H\$10,MATCH(A2,'[Sample-Level.xlsx]Sheet1'!\$A\$2:\$A\$10,0),0))),"")

where it is assumed the table is in '[Sample-Level.xlsx]Sheet1'!\$C\$1:\$H\$10 and the Store you want to look up is in A2 of your active (formula) sheet.

Updated know I am getting all zeros.
See attached.

I put the formula in Sheet2., but you can CUT (not Copy) and Paste in another workbook, and it should update, you may need to just change the A2 in the formula to coincide to where the Store you want to lookup is in that workbook.

Also you will need to adjust the ranges if they are really larger than to row 9;
Copy-of-Sample-Level-1.xlsx

I am getting mixed results, some rows are pulling the correct level and others are not.
Test-Cost-Variance.xlsx
Test-Forecast.xls