Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with multiple fields that need to match
Hi, I'm trying to search two tables to find data that correlates with 3
columns (my unique identifier). In one table, I have 3 columns City Name, Dest City, Dest State In another table, I have multiple columns, which include City Name, Dest City, Dest State & Price I want to search the 2nd table to find the Price that correlates with the exact match of City Name + Dest City + Dest State, and return that Price to me in the first table where an exact match was found. Example - In the Table with Data City Name Dest City Dest State Price Dallas Fac Chicago IL 2.01 Atlanta Fac Houston TX 3.01 Dallas Fac Austin TX 4.01 In the target table, there might be rows with City Name, Dest City & Dest State that match, and some that don't. For those rows where everything matches, I want it to pull in the info that correlates it from the original table like: City Name Dest City Dest State Price Dallas Fac Chicago IL 2.01 Dallas Fac Seattle WA Atlanta Fac Houston TX 3.01 Dallas Fac Austin TX 4.01 Atlanta Fac Abilene TX Help? Thanks, Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with multiple fields that need to match
I just answered a similar post..
If you have 3 columns in sheet1 and four in sheet2, enter this in D1 of sheet1 to get the price... =INDEX(Sheet2!$D$1:$D$10,MATCH(1,(Sheet2!$A$1:$A$1 0=A1)*(Sheet2!$B$1:$B$10=B1)*(Sheet2!$C$1:$C$10=C1 ),0)) [adjust 10 to the last row in your set] and press CTRL-SHIFT-ENTER and copy down... It will give you 0 in case no match is found... "Mark" wrote: Hi, I'm trying to search two tables to find data that correlates with 3 columns (my unique identifier). In one table, I have 3 columns City Name, Dest City, Dest State In another table, I have multiple columns, which include City Name, Dest City, Dest State & Price I want to search the 2nd table to find the Price that correlates with the exact match of City Name + Dest City + Dest State, and return that Price to me in the first table where an exact match was found. Example - In the Table with Data City Name Dest City Dest State Price Dallas Fac Chicago IL 2.01 Atlanta Fac Houston TX 3.01 Dallas Fac Austin TX 4.01 In the target table, there might be rows with City Name, Dest City & Dest State that match, and some that don't. For those rows where everything matches, I want it to pull in the info that correlates it from the original table like: City Name Dest City Dest State Price Dallas Fac Chicago IL 2.01 Dallas Fac Seattle WA Atlanta Fac Houston TX 3.01 Dallas Fac Austin TX 4.01 Atlanta Fac Abilene TX Help? Thanks, Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match multiple fields | Excel Worksheet Functions | |||
Vlookup with Multiple Columns to Match | Excel Discussion (Misc queries) | |||
Vlookup to look up multiple fields? | Excel Discussion (Misc queries) | |||
Multiple If/Vlookup/Match | Excel Worksheet Functions | |||
Please Help! vlookup & match with multiple variables | Excel Worksheet Functions |