How to lookup with more than 1 value

[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?

 

[Sheeloo]

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

Pl. give an example.

 

[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.

 

[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

 

[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,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,4,False)),"",VLOOKUP(A2&B2,
Sheet2!A,4,False))

 

[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.

 

[Alan]

Got it, thank you

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,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,4,False)),"",VLOOKUP(A2&B2,
Sheet2!A,4,False))