Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it possible to select and change the values of cells in a minimized excel spread sheet from vba? | Excel Worksheet Functions | |||
Marcro recroding/ stop Ikon not appearing in middle of excel sheet | Excel Discussion (Misc queries) | |||
Excel 2002 "Protect Sheet", but Allow "Insert Comments"? | Excel Worksheet Functions | |||
make an excel worksheet (sheet 2) open w/ the cursor located in t. | Excel Discussion (Misc queries) | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel |