Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Spence
 
Posts: n/a
Default Excel: match two cells in one sheet to two cells in another and return a third cells value

I want to reference cells in two columns in sheet1 and when the
criteria matches cells in two columns of sheet2 I want to return the
value in the third column of sheet1 to the third column of sheet2?

Does that make sense? Vlookup won't work here because I want to match
two columns in the same array. I thought Indexing would work but I
keep coming up with an error. I also tried to do a Match/Offset
formula from Chip Pearson's website but that wasn't what I was looking
for. Any help is much appreciated.

Thanks,
Spence

  #2   Report Post  
Dave Breitenbach
 
Posts: n/a
Default

using ranges of b2:d6 in both sheet 1 and sheet2, I used this formula in
sheet 2, cell d2

=SUMPRODUCT(--(Sheet1!$B$2:$B$6=Sheet2!B2),--(Sheet1!$C$2:$C$6=Sheet2!C2),--(Sheet1!$D$2:$D$6))

hth,
dave

"Spence" wrote:

I want to reference cells in two columns in sheet1 and when the
criteria matches cells in two columns of sheet2 I want to return the
value in the third column of sheet1 to the third column of sheet2?

Does that make sense? Vlookup won't work here because I want to match
two columns in the same array. I thought Indexing would work but I
keep coming up with an error. I also tried to do a Match/Offset
formula from Chip Pearson's website but that wasn't what I was looking
for. Any help is much appreciated.

Thanks,
Spence


  #3   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Can you post an example?

I'll bet you want something like this:

=INDEX(third column of sheet1,MATCH(two columns of sheet2,two columns in
sheet1,0))

If you had said where all this stuff is specifically located, then the
formula would look something like this:

=INDEX(Sheet1!C1:C100,MATCH(A1&B1,Sheet1!A1:A100&S heet1!B1:B100,0))

Array entered.

When you include details we don't have to guess!

Biff

"Spence" wrote in message
oups.com...
I want to reference cells in two columns in sheet1 and when the
criteria matches cells in two columns of sheet2 I want to return the
value in the third column of sheet1 to the third column of sheet2?

Does that make sense? Vlookup won't work here because I want to match
two columns in the same array. I thought Indexing would work but I
keep coming up with an error. I also tried to do a Match/Offset
formula from Chip Pearson's website but that wasn't what I was looking
for. Any help is much appreciated.

Thanks,
Spence



  #4   Report Post  
Junior Member
 
Posts: 1
Post

Hi All,

My question is simple. below is the table. I want to match two cells in first set of data with the second set of data and return the value of Column "Job Status" in first set from the second set data.
First Set
Date Aired Home Team Away Team Job Status
23-Oct-10 Dallas Stars Nashville Predators
7-Oct-10 Minnesota Wild Carolina Hurricanes
7-Oct-10 Toronto Maple Leafs Montreal Canadiens
7-Oct-10 Pittsburgh Penguins Philadelphia Flyers


Second Set


Date Home Team Away Team Job Status
23-Oct-10 Dallas Stars Nashville Predators Upload Complete
26-Oct-10 Dallas Stars Anaheim Ducks Upload Complete
30-Oct-10 Dallas Stars Buffalo Sabres Upload Complete
3-Nov-10 Dallas Stars Pittsburgh Penguins Upload Complete
1-Feb-11 Dallas Stars Vancouver Canucks VD Created


Please mail me the solution for this :

Quote:
Originally Posted by Biff View Post
Hi!

Can you post an example?

I'll bet you want something like this:

=INDEX(third column of sheet1,MATCH(two columns of sheet2,two columns in
sheet1,0))

If you had said where all this stuff is specifically located, then the
formula would look something like this:

=INDEX(Sheet1!C1:C100,MATCH(A1&B1,Sheet1!A1:A100&S heet1!B1:B100,0))

Array entered.

When you include details we don't have to guess!

Biff

"Spence" wrote in message
oups.com...
I want to reference cells in two columns in sheet1 and when the
criteria matches cells in two columns of sheet2 I want to return the
value in the third column of sheet1 to the third column of sheet2?

Does that make sense? Vlookup won't work here because I want to match
two columns in the same array. I thought Indexing would work but I
keep coming up with an error. I also tried to do a Match/Offset
formula from Chip Pearson's website but that wasn't what I was looking
for. Any help is much appreciated.

Thanks,
Spence
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
is it possible to select and change the values of cells in a minimized excel spread sheet from vba? Daniel Excel Worksheet Functions 6 July 11th 05 11:24 PM
Marcro recroding/ stop Ikon not appearing in middle of excel sheet vishu Excel Discussion (Misc queries) 3 July 6th 05 11:37 PM
Excel 2002 "Protect Sheet", but Allow "Insert Comments"? VP Safe Excel Worksheet Functions 2 July 5th 05 07:18 PM
make an excel worksheet (sheet 2) open w/ the cursor located in t. animated text in excel worksheet Excel Discussion (Misc queries) 0 February 10th 05 11:29 PM
Hyperlink to specific sheet in Excel Web File jd17 Links and Linking in Excel 0 December 8th 04 10:03 PM


All times are GMT +1. The time now is 05:17 PM.

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"