CODEWITHSUNDEEP
excel
Spencer
Spencer

Reputation: 642

Get cell value where column value equals row/column value in second sheet

I have two sheets:

Sheet 1:

Callsign  |  Dep ICAO  |  Arr ICAO  | Route
SWA215       KBUR         KOAK
AAL258       KLAS         KLAX

Sheet 2:

DEP ->  | KLAX          | KBUR        | KLAS        | KSAN
KSFO    | some route1   some route4   some route7   some route10
KOAK    | some route2   some route5   some route8   some route11
KLAX    | some route3   some route6   some route9   some route12
ARR ^

Basically I need to find the route value in Sheet 2 where the DEP & ARR column equals the Dep ICAO and Arr ICAO in Sheet 1.

So for example, in sheet one, the route value for SWA215 should return some route5. The route value for AAL258 should return some route9.

I've tried messing with VLOOKUP but I wasn't sure how to incorporate two different sheets. Here's what I had for the function:

=VLOOKUP(B2:C2,Sheet 2!A1:Q18,Sheet 2!B2:E4) but it returned #VALUE! error.

Upvotes: 2

Views: 1718

Answers (1)

neophyte
neophyte

Reputation: 1816

You need INDEX with two MATCH(Two Way Lookup). Try the below example:

=INDEX(Sheet2!C3:E6,MATCH(Sheet1!D4,Sheet2!B3:B6,0),MATCH(Sheet1!C4,Sheet2!C2:E2,0))

Where Sheet2!C3:E6 is the route range(data you need). The first MATCH matches the arr in arr col and MATCH 2 matches the dep in dep row. In this example formula should be placed in sheet1 cell E4

Upvotes: 2

Related Questions