jmac001

asked on

# 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

Sample-Level.xlsx

ASKER

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

Yes, just add the paths

e.g

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

e.g

=IFERROR(INDEX('[Sample-Le

ASKER

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.

ASKER

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.

If so,

=IFERROR(INDEX('[Sample-Le

where it is assumed the table is in '[Sample-Level.xlsx]Sheet1

ASKER

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

ASKER

I am getting mixed results, some rows are pulling the correct level and others are not.

Test-Cost-Variance.xlsx

Test-Forecast.xls

Test-Cost-Variance.xlsx

Test-Forecast.xls

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you so much I did adjust, but only to 2 didn't go far enough/

=IFERROR(INDEX($C$2:$H$2,M

copied down.

Adjust ranges to suit.