Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single formula to find match across four columns?
I'm sure there is a way to do this, I'm just having trouble with the syntax.
On Sheet1, I have some values in Columns A and C On Sheet 2, I have some values in Column D and J. For any given row, I need to locate where in the other sheet the value in Sheet1(A) matches the value in Sheet2(D) /and/ the value in Sheet1(C) matches the value in Sheet2(J). These sheets will be continuously updated and sorted by other folks, so I need to stay away from more complicated solutions like using an extra column to concatenate A&C in Sheet1 and a column to concatenate D&J in Sheet2, then match against those- I'm trying to craft a match statement that will include the concatenation in the formula itself. =MATCH(D2&"."&J2,('Raw'!A1:A65000)&"."&('Raw'!A1:C 65000),FALSE) There is the possibility of an overlap in the concatenated strings, so I'm also adding a character that never shows up in the raw reports to ensure that I don't confuse "cat" & "stick" with "cats" and "tick"; instead I'll compare cat.stick with cats.tick and realize they don't match. Any ideas? Thanks! Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single formula to find match across four columns?- solved
I swear, it always happens- I can struggle for minutes or hours with a
problem, and it seems like half the time I find the solution within 5 minutes of posting my question. First, I had a typo in my formula. Corrected formula is: =MATCH(D2&"."&J2,('Raw'!A1:A65000)&"."&('Raw'!C1:C 65000),FALSE) once I did that, it worked as an array formula. Sorry for the extra bandwidth <sigh Keith "Keith R" wrote in message ... I'm sure there is a way to do this, I'm just having trouble with the syntax. On Sheet1, I have some values in Columns A and C On Sheet 2, I have some values in Column D and J. For any given row, I need to locate where in the other sheet the value in Sheet1(A) matches the value in Sheet2(D) /and/ the value in Sheet1(C) matches the value in Sheet2(J). These sheets will be continuously updated and sorted by other folks, so I need to stay away from more complicated solutions like using an extra column to concatenate A&C in Sheet1 and a column to concatenate D&J in Sheet2, then match against those- I'm trying to craft a match statement that will include the concatenation in the formula itself. =MATCH(D2&"."&J2,('Raw'!A1:A65000)&"."&('Raw'!A1:C 65000),FALSE) There is the possibility of an overlap in the concatenated strings, so I'm also adding a character that never shows up in the raw reports to ensure that I don't confuse "cat" & "stick" with "cats" and "tick"; instead I'll compare cat.stick with cats.tick and realize they don't match. Any ideas? Thanks! Keith |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single formula to find match across four columns?
match(1,(d2=raw!$a$1:$a$100)*(j2=raw!$c$1:$c$100), 0)
Still an array formula. Watch that range in the last portion: 'Raw'!A1:C65000 You wanted 'Raw'!C1:C65000 (I think) Keith R wrote: I'm sure there is a way to do this, I'm just having trouble with the syntax. On Sheet1, I have some values in Columns A and C On Sheet 2, I have some values in Column D and J. For any given row, I need to locate where in the other sheet the value in Sheet1(A) matches the value in Sheet2(D) /and/ the value in Sheet1(C) matches the value in Sheet2(J). These sheets will be continuously updated and sorted by other folks, so I need to stay away from more complicated solutions like using an extra column to concatenate A&C in Sheet1 and a column to concatenate D&J in Sheet2, then match against those- I'm trying to craft a match statement that will include the concatenation in the formula itself. =MATCH(D2&"."&J2,('Raw'!A1:A65000)&"."&('Raw'!A1:C 65000),FALSE) There is the possibility of an overlap in the concatenated strings, so I'm also adding a character that never shows up in the raw reports to ensure that I don't confuse "cat" & "stick" with "cats" and "tick"; instead I'll compare cat.stick with cats.tick and realize they don't match. Any ideas? Thanks! Keith -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find match formula | Excel Worksheet Functions | |||
Find A Match Formula | Excel Worksheet Functions | |||
Index Match Two columns Find Last Bottom | Excel Worksheet Functions | |||
Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
Find match between 2 columns and then record data that is in colum | Excel Discussion (Misc queries) |