Vlookup Function
 

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.

Vlookup Function
 

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)*(Shee t1!$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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote:
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.

Vlookup Function
 

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?

"Max" wrote:

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)*(Shee t1!$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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote:
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.

Vlookup Function
 

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 befo

=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)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote:
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?

Vlookup Function
 

.. 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).

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Vlookup Function
 

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

SS

"Max" wrote:

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 befo

=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)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote:
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?

Vlookup Function
 

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"?

"Max" wrote:

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 befo

=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)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote:
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?

Vlookup Function
 

Pleased to hear that. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote in
message ...
Perfect! It works great now! Thanks Max!!!
SS

Vlookup Function
 

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

=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,MA TCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet 1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2 &""),0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote:
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"?

Vlookup Function
 

Thanks Max! That covers everything!


"Max" wrote:

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

=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,MA TCH(1,(TRIM(Sheet1!$A$2:$A$100)=A2&"")*(TRIM(Sheet 1!$B$2:$B$100)=B2&"")*(TRIM(Sheet1!$C$2:$C$100)=C2 &""),0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote:
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"?

Vlookup Function
 

Glad it did. Cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Secret Squirrel" wrote in
message ...
Thanks Max! That covers everything!