multiple column match

  • Thread starter Thread starter bluesingingcat
  • Start date Start date
B

bluesingingcat

I need to match 3 columns on one worksheet to 3 columns on
another worksheet to give me the result of the 4th column
of the 1st worksheet. I have been trying to combine an IF
statement with VLOOKUP but can't seem to get it.
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet1'!$D$1:$D$100,MATCH(A1&B1&C1,'sheet1'$A$1:$A$100 &
'sheet1'$B$1:$B$100 & 'sheet1'$C$1:$C$100,0)
 
Try INDEX and MATCH:

=INDEX(Sheet1!D1:D10,MATCH(Sheet2!A1&Sheet2!B1&Sheet2!
C1,Sheet1!A1:A10&Sheet1!B1:B10&Sheet1!C1:C10,0))

Array-entered (press ctrl/shift/enter).

In this example, the formula is merging A1 & B1 & C1 on
Sheet2 and comparing to the merge of columns A & B & C on
Sheet1 (the actual range is rows 1-10). The first match
it finds will return the value of the same row in col. D
of Sheet1.

HTH
Jason
Atlanta, GA
 
Let A2:D100 on Sheet2 house the table from which you want to retrieve...

Insert a new column before the current column A.

In A2 enter & copy down:

=B2&CHAR(127)&C2&CHAR(127)&D2

Let Sheet1 house the lookup values of interest from A2 on...

In D2 enter:

=VLOOKUP(A2&CHAR(127)&B2&CHAR(127)&C2,Sheet2!$$A$2:$E$100,5,0)
 
Back
Top