Complex Lookup Question

[TB@work]

Here is what my spreadsheet looks like

Load# Stop Stop Seq. Final Dest.
1234 Dayton 1 Columbus
1234 Dublin 2 Columbus


I want a formula that will look by load number, then stop seq. and end with
the location. For instance I would want to find stop seq. 1 for load 1234
to return Dayton. Thanks for your help.

 

[ryguy7272]

With your data in A23, and Load# in F2 and StopSeq. in G2, enter this in H2:
=INDEX(B2:B101, MATCH(F2&G2,A2:A101&C2:C101,0))

Hit Ctrl + Shift + Enter, not just Enter

 

[T. Valko]

Nit pick...

When the data is potentially ambiguous concatenating can cause problems.

123...Canton...41...Salem
1234...Dayton...1...Columbus
1234...Dublin...2...Columbus

F2 = 1234
G2 = 1

=INDEX(B2:B101, MATCH(F2&G2,A2:A101&C2:C101,0))

Returns Canton when the correct result should be Dayton.

If you're going to concatenate it's usually better to add a "delimiter" to
make every combination unique.

=INDEX(B2:B10, MATCH(F2&"^^"&G2,A2:A10&"^^"&C2:C10,0))

Returns the correct result, Dayton.

Another way...

=INDEX(B2:B10, MATCH(1,IF(A2:A10=F2,IF(C2:C10=G2,1)),0))

Or...

=INDEX(B2:B10, MATCH(1,(A2:A10=F2)*(C2:C10=G2),0))

The IF version is slightly more efficient on large ranges.