Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checks whether a value is text or number | Excel Worksheet Functions | |||
Checks whether a value is text or number | Excel Worksheet Functions | |||
Different colored checks?? | Excel Discussion (Misc queries) | |||
Formula/function that checks for a set of values. | Excel Worksheet Functions | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions |