Vlookup that checks against two values
 

I have a sheet that contains a column of Product numbers, a comlumn of
customer numbers, and a column for orders. In another sheet I have the
a similar format with the same columns. I need to check I need to move
the values of the order to the second sheet but I must check against
both the product number and the customer number. excel does not allow
you to do the following (gives the #NAME? error).

VLOOKUP(A1 AND B1, ANOTHERSHEETA1:C1,3,false)

What is the best way to do this?

John

Vlookup that checks against two values
 

VLOOKUP(A1 AND B1, ANOTHERSHEETA1:C1,3,false)

One way is to create a concat col in the source sheet as the 1st col (lookup
col) for the vlookup. Then the above concept will work.

Another way is to use an array-entered* index/match,
something like this:
=index(ReturnCol,match(1,(ColA=A1)*(ColB=B1),0))

Eg, if source data is in a sheet: x,
in your destination sheet,
you could array-enter* this in C1:
=INDEX(x!$C$1:$C$100,MATCH(1,(x!$A$1:$A$100=A1)*(x !$B$1:$B$100=B1),0))
and copy C1 down

*Array-enter means to press CTRL+SHIFT+ENTER to confirm the formula, instead
of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"runsrealfast" wrote in message
...
I have a sheet that contains a column of Product numbers, a comlumn of
customer numbers, and a column for orders. In another sheet I have the
a similar format with the same columns. I need to check I need to move
the values of the order to the second sheet but I must check against
both the product number and the customer number. excel does not allow
you to do the following (gives the #NAME? error).



What is the best way to do this?

John

Vlookup that checks against two values
 

You have responses at all your other posts, too!

runsrealfast wrote:

I have a sheet that contains a column of Product numbers, a comlumn of
customer numbers, and a column for orders. In another sheet I have the
a similar format with the same columns. I need to check I need to move
the values of the order to the second sheet but I must check against
both the product number and the customer number. excel does not allow
you to do the following (gives the #NAME? error).

VLOOKUP(A1 AND B1, ANOTHERSHEETA1:C1,3,false)

What is the best way to do this?

John

--

Dave Peterson

Vlookup that checks against two values
 

Howdy, Dave <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Peterson" wrote in message
...
You have responses at all your other posts, too!

Vlookup that checks against two values
 

I saw your post, too!

Max wrote:

Howdy, Dave <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Peterson" wrote in message
...
You have responses at all your other posts, too!

--

Dave Peterson