How to lookup with more than 1 value

A

Alan

I understand that vlookup can only lookup value with only 1 arguement.
If I need to lookup for a value with more than 1 arguement, which function
in Excel can I use?
 
S

Sheeloo

What do you mean by "lookup for a value with more than 1 arguement"?

Pl. give an example.
 
A

Alan

Example

I have a data sheet with these header

Destination, Service Type

And another sheet with

Destination, Service Type and Charges

I need to look into both Destination and Service Type to find out or return
the value that match these 2 arguement. How can I do that? Vlookup only allow
me to lookup base on only 1 arguement. Thank you.
 
A

Alan

Data Sheet
Destination Port Service Type Freight (Chargeable weight)
PVG DD How do I lookup from the table below
PVG DD to return the correct value that match
PVG DD the 2 column on the left "Destination
PVG DD Port" & "Service Type"?
PVG DD


Destination Port Service Type Freight (Chargeable weight)
PVG AA 1.13
PVG AD 1.19
PVG DA 1.22
PVG DD 1.28
 
S

Sheeloo

In the sheet with Destination, Service Type and Charges (let us say Sheet2)
insert Col A and enter formula
=B1&C1
and copy down

Now in the sheet with Destination, Service Type
enter this in C2 (assuming header rows)

=VLOOKUP(A2&B2, Sheet2!A:D,4,False)
and copy down

You can wrap VLOOKUP like the following to avoid #N/A for missing values
=IF(ISNA(VLOOKUP(A2&B2, Sheet2!A:D,4,False)),"",VLOOKUP(A2&B2,
Sheet2!A:D,4,False))
 
T

T. Valko

Try this:

=SUMPRODUCT(--(Sheet2!A$1:A$100=A1),--(Sheet2!B$1:B$100=B1),Sheet2!C$1:C$100)

Copy down as needed.
 
A

Alan

Got it, thank you :)

Sheeloo said:
In the sheet with Destination, Service Type and Charges (let us say Sheet2)
insert Col A and enter formula
=B1&C1
and copy down

Now in the sheet with Destination, Service Type
enter this in C2 (assuming header rows)

=VLOOKUP(A2&B2, Sheet2!A:D,4,False)
and copy down

You can wrap VLOOKUP like the following to avoid #N/A for missing values
=IF(ISNA(VLOOKUP(A2&B2, Sheet2!A:D,4,False)),"",VLOOKUP(A2&B2,
Sheet2!A:D,4,False))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top