Index,match,match

[kbkrueger]

HELP!
I'm trying to write an Index,Match,Match formula to return a value from a cell based on 2 criteria.
I need it to return the cell value in the "CNT" column which corresponds to the "GSX/AVI" row and "ANC" from the REGION column and "ANC" from the "FLIGHT ORIGIN" column. So for example:ANC - ANC - GSX/AVI = 198

I've been struggling with this for the past week and it's making me crazy. I haven't any code to display as the only thing I've been able to get to work is =VLOOKUP("*anc*",A12:M152,10,FALSE) but that doesn't return the value for GSX/AVI

All help greatly appreciated,
Thank you in advance!

[TMS]

Please Login or Register  to view this content.

works but I don't know if you can make it work elsewhere.

Regards, TMS

[FDibbins]

Do A:E always only have an entry in 1 row (and is it necessary to have A:B and D:E merged?)

[JeteMc]

I added three helper columns that trimmed the data in columns A,C and D (as B is merged with A).
The formula that I put in B, which was selected when I opened the file so I assumed that is where the formula should go, was:

Please Login or Register  to view this content.

This is the file with the formula applied:Copy of Operational View - Daily.xlsx

Hope this helps.

[kbkrueger]

Do A:E always only have an entry in 1 row (and is it necessary to have A:B and D:E merged?)

When expanded the column FLIGHT ORIGIN will have a repeat of each of the city codes in the REGION column. Ideally I suppose I could use the name ANC in the FLIGHT ORIGIN column instead of the REGION column.
As for the merged columns, this is how the raw data comes from the main server.

[kbkrueger]

JeteMc - Thank you! This just may work. I will now have to figure how to work that into the macro I've built to format the full workbook!

[FDibbins]

Try this withoutthe helper, it assumes that your data is repeated in column C...

	A	B	C	D	E
9		198	ANC	GSX/AVI
10
11	Region		Flight Origin	Product
12	SEA		SEA

B9=INDEX($F$12:$M$152,MATCH("*"&C9&"*"&" "&D9,INDEX($C$12:$C$152&" "&$D$12:$D$152,0),0),MATCH("CNT",$F$11:$M$11,0))
C9 and D9 are your search criteria

[JeteMc]

Glad that you found something that works for you. Please mark the thread 'Solved' and if anyone provided helpful information please consider adding to their reputation.