Link to home
Start Free TrialLog in
Avatar of jmac001
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
Avatar of NBVC
NBVC
Flag of Canada image

Try:

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

copied down.

Adjust ranges to suit.
Avatar of jmac001
jmac001

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)
Avatar of jmac001

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.
Avatar of jmac001

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.
Avatar of jmac001

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
Avatar of jmac001

ASKER

I am getting mixed results, some rows are pulling the correct level and others are not.
Test-Cost-Variance.xlsx
Test-Forecast.xls
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jmac001

ASKER

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