find concatenated values question

  • Thread starter Thread starter Gary Keramidas
  • Start date Start date
G

Gary Keramidas

i have to find values based on 3 non-contiguous columns in another worksheet. i
can concatenate the string i'm looking for, but what's the best way to find that
string in 3 non-contiguous columns in another worksheet.

tried union as the range to search but couldn't get it to work.

just looking for ideas, no need to write any code.

thanks
 
If I understand you, you have a value "NC123ABCD" and you want to find the
row in another worksheet where "NC" is in column 3, "123" in column 5 and
"ABCD" in column 8. Something like that?

Hmm, I've never tried anything like that. My first thought would be to
create a new column in the other worksheet in which each formula read
=RC3&RC5&RC8 (in the R1C1 notation, obviously) and then do a normal VLOOKUP
or some other kind of Find function using that column instead of trying to
match on the individual columns. Is that possible?

Well, sure it is; even if it's not your workbook and you're not allowed to
modify it, you can have your program open up a read-only copy of it, create
the extra column, use it and then throw it away when you're done; open it
read-only like that and Excel won't allow you to save the changes even
accidentally. How's that?
 
How about using MATCH

On Error Resume Next
val = ActiveSheet.Evaluate("MATCH(1,(A2:A200=""" & value1 &
""")*(C2:C200=""" & value2 & """)*(E2:E200=""" & value3 & """),0)")
If Not IsError(val) Then

'....
End If
 
Back
Top