Complex Lookup Question
 

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.

Complex Lookup Question
 

With your data in A2:D3, 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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"TB@work" wrote:

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.

Complex Lookup Question
 

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
With your data in A2:D3, 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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"TB@work" wrote:

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.