How to lookup with more than 1 value
 

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?

How to lookup with more than 1 value
 

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

Pl. give an example.

"Alan" wrote:

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?

How to lookup with more than 1 value
 

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.

"Sheeloo" wrote:

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

Pl. give an example.

"Alan" wrote:

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?

How to lookup with more than 1 value
 

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


"Alan" wrote:

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.

"Sheeloo" wrote:

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

Pl. give an example.

"Alan" wrote:

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?

How to lookup with more than 1 value
 

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))


"Alan" wrote:

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.

"Sheeloo" wrote:

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

Pl. give an example.

"Alan" wrote:

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?

How to lookup with more than 1 value
 

Try this:

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Alan" wrote in message
...
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


"Alan" wrote:

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.

"Sheeloo" wrote:

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

Pl. give an example.

"Alan" wrote:

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?

How to lookup with more than 1 value
 

Got it, thank you :)

"Sheeloo" wrote:

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))


"Alan" wrote:

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.

"Sheeloo" wrote:

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

Pl. give an example.

"Alan" wrote:

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?