Vlookup Function

  • Thread starter Secret Squirrel
  • Start date
S

Secret Squirrel

I have a worksheet that I'm trying to match up data against another.
In the worksheet that has the detail I have 3 columns that I need to match.
I want to have it return a value of "3" if it matches all 3 of the columns
and a "0" if it doesn't. My summary sheet has the same 3 columns so I need to
find exact matches for both worksheets for those 3 columns. I hope I'm making
sense here. I assume I would use the "VLookup" function but I'm unsure how to
write the code out to find matches for all 3 columns.
 
M

Max

One way,

Assuming the 3 source cols are cols A to C in Sheet1, data from row 2 to 100.

Try something like this in say, Sheet2's D2, array-entered with CSE**
=IF(ISNUMBER(MATCH(1,(Sheet1!$A$2:$A$100=A2)*(Sheet1!$B$2:$B$100=B2)*(Sheet1!$C$2:$C$100=C2),0)),3,0)
Copy D2 down to return correspondingly for other sets of inputs in A3:C3,
A4:C4, etc

**press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER
 
S

Secret Squirrel

Hi Max,

Thanks for that code. But I'm getting all 0's for everything. Could it be
that the detail in column A is a combination of text and numbers? Is the code
only going to work if the data is all numbers?
 
M

Max

Try this variation which might better suit the possible underlying data types
(text & text numbers mixed with real numbers) that you're trying to match.
Array-entered with CSE, as before:

=IF(ISNUMBER(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2&""),0)),3,0)
 
M

Max

.. But I'm getting all 0's for everything ..

Another point ... The above could also be symptomatic that the earlier
expression was not **array-entered** correctly. If array-entered correctly,
Excel will wrap curly braces: { } around the formula. See these curly braces
appear in the formula bar as a visual check that it's array-entered
correctly. If you don't see it, then the expression hasn't been entered
correctly and it'll return wrong results (all you'd get are zeros).
 
S

Secret Squirrel

Perfect! It works great now! Thanks Max!!!

SS

Max said:
Try this variation which might better suit the possible underlying data types
(text & text numbers mixed with real numbers) that you're trying to match.
Array-entered with CSE, as before:

=IF(ISNUMBER(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2&""),0)),3,0)
Click to expand...
 
S

Secret Squirrel

One follow up question...

Say instead of having it show a "3" if the match is true, could I have it
show another value from the detail worksheet? In column E of my detail
worksheet I have a record number. How could I show that value instead of a
"3"?
 
M

Max

Try something along the lines of this amendment,
array-entered with CSE, as before:

=IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2&""),0)),"",INDEX(Sheet1!$E$2:$E$100,MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2&""),0)))
 
S

Secret Squirrel

Thanks Max! That covers everything!


Max said:
Try something along the lines of this amendment,
array-entered with CSE, as before:

=IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2&""),0)),"",INDEX(Sheet1!$E$2:$E$100,MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2&""),0)))
Click to expand...
 
Top