Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on multiple values
I have the following data in 3 columns
A B C TOP BULB 1 TOP SCREW 5 TOP LIGHT 1 TOP LAMP 1 TOP PAPER 1 BOTTOM BULB 5 BOTTOM SCREW 4 BOTTOM LIGHT 2 BOTTOM LAMP 8 BOTTOM PAPER 7 I want to be able to first match against the value in column a then find the match in column b once it finds both match then return the result of column 3. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on multiple values
Insert a new column A.
In the new column A concatenate the data from what is now column B and column C. Example of a row two formula: =B2&" "&C2 Now you can use a vLookUp function on Column A with an offset of 4 to return data from what is now column D. Good Luck. "samuel" wrote: I have the following data in 3 columns A B C TOP BULB 1 TOP SCREW 5 TOP LIGHT 1 TOP LAMP 1 TOP PAPER 1 BOTTOM BULB 5 BOTTOM SCREW 4 BOTTOM LIGHT 2 BOTTOM LAMP 8 BOTTOM PAPER 7 I want to be able to first match against the value in column a then find the match in column b once it finds both match then return the result of column 3. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on multiple values
1) If you can add a new A column:
In new A1 use =B1&C1 With BOTTOM in G1 and SCREW in H1, use =VLOOKUP(G1&H1,A1:D10,4,FALSE) 2) If you cannot insert, then add a new column (I will assume in D but anywhere is OK) In D1 use =A1&B1 With BOTTOM in G1 and SCREW in H1, use =INDEX(D1:D10,MATCH(G1&H1,A1:A10,0)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "samuel" wrote in message ... I have the following data in 3 columns A B C TOP BULB 1 TOP SCREW 5 TOP LIGHT 1 TOP LAMP 1 TOP PAPER 1 BOTTOM BULB 5 BOTTOM SCREW 4 BOTTOM LIGHT 2 BOTTOM LAMP 8 BOTTOM PAPER 7 I want to be able to first match against the value in column a then find the match in column b once it finds both match then return the result of column 3. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on multiple values
=SUMPRODUCT(--(A1:A100="Top"),--(B1:B100="Bulb),C1:C100)
"samuel" wrote: I have the following data in 3 columns A B C TOP BULB 1 TOP SCREW 5 TOP LIGHT 1 TOP LAMP 1 TOP PAPER 1 BOTTOM BULB 5 BOTTOM SCREW 4 BOTTOM LIGHT 2 BOTTOM LAMP 8 BOTTOM PAPER 7 I want to be able to first match against the value in column a then find the match in column b once it finds both match then return the result of column 3. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup multiple values | Excel Worksheet Functions | |||
Vlookup for multiple values | Excel Worksheet Functions | |||
How do I allow for multiple values in VLOOKUP? | Excel Worksheet Functions | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions | |||
VLOOKUP FOR MULTIPLE VALUES | Setting up and Configuration of Excel |