Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 989
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match multiple fields Belinda7237 Excel Worksheet Functions 5 October 21st 08 05:18 PM
Vlookup with Multiple Columns to Match pt_lily Excel Discussion (Misc queries) 2 August 26th 08 01:02 AM
Vlookup to look up multiple fields? Cam Excel Discussion (Misc queries) 5 February 13th 08 08:21 PM
Multiple If/Vlookup/Match Deeds Excel Worksheet Functions 3 November 30th 06 04:23 PM
Please Help! vlookup & match with multiple variables ivygirl Excel Worksheet Functions 2 January 7th 06 10:46 AM


All times are GMT +1. The time now is 09:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"