Lookup based on two criteria

[Saturn]

I have a data list which I need to look up some info based one two search criteria.

Please see the enclosed workbook which (I hope) describes what the purpose is.

Saturn

[Fotis1991]

Hi

Try this array formula for Frequency and copy across for headings

=IFERROR(INDEX(G$4:G$20,MATCH("SYCJ"&291,$C$4:$C$20&$H$4:$H$20,0)),"")

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[XOR LX]

Hi,

Based on the table you provided, and, assuming that the ICAO code and Runway ID for which you wish to return the Frequency are in cells A1 and B1 respectively, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in cell C1:

=INDEX(G4:G13,MATCH(A1&B1,C4:C13&F4:F13,0))

Change G4:G13 in above to H4:H13 to return the Heading.

Regards

[Ace_XL]

Try this array formula

With
Frequency in B16 and
Heading in B16

=INDEX($C$4:$C$13,MATCH(B16&B17,$G$4:$G$13&$H$4:$H$13,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Saturn]

Thank you very much Fotis 1991, XOR LX and ACE_XL.
To Fotis 1991: what does "&291" in the formula stand for ?

XOR LX' formula works perfect!

[Fotis1991]

T
To Fotis 1991: what does "&291" in the formula stand for ?

Exactly same job that does the B1 IN XOR LX'S formula. He used cell reference, i used straight the values that you described in your sample worksheet!

In fact,All three of us, suggested the same formula.

[Saturn]

Yes, you did. Thanks again!

[Fotis1991]

........................