Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Function
Glad it did. Cheers.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Secret Squirrel" wrote in message ... Thanks Max! That covers everything! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Embedded VLOOKUP function within IF function | Excel Worksheet Functions | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |