Combine Vlookup with If & OR statements?

[ouroboros]

I'm trying to get this equation to look for data that approximately matches data in 3 columns. In this case the data is usernames and I need to match one of three potential user names to ones that are in a different sheet column. Currently this is the equation that I am trying to use.

=IF(VLOOKUP(OR(J26,K26,L26),'data'!E:E,5,TRUE)=OR(J26,K26,L26), "Match", "No Match")

Every time I try and do this I get a #VALUE error. Can anyone here help me out?

 

[etaf]

you say

approximately matches data in 3 columns

this will only work on an exact match
OR if you use wild cards and the text is within other text

it wont work for things like
george LTD in one sheet
H & R George & Sons Limited in another sheet
you could try matching the george part using *george*

 

[ouroboros]

Well what I'm trying to do - and perhaps I didn't say this clearly enough - is that each person has three usernames. I am trying to match one of those usernames to one in a list of usernames in another tab. I have exact copies of the usernames, but sometimes they have slight deviations because the usernames are global and sometimes have special characters. Thats why I used "true" in the equation because it says it will look for "approximate matches".

 

[etaf]

Thats why I used "true" in the equation because it says it will look for "approximate matches".

no TRUE will not really use approximate in the way you mean

lets say you had a list of number
1,2,4,5,6,8,10

and your lookup was 3
with true set , it will return the number 2
with false set , it will return #N/A

can you provide some examples of usernames that are not exact , but you want to match - just so we have an idea of what sort of matches you want to achieve

 

[Aladin Akyurek]

Something like...

Either...

=LOOKUP(9.99999999999999E+307,MATCH(data!E:E,$J$26:$L$26,0),data!E:E)

Or...

=ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(data!E:E,$J$26:$L$26,0)))+0

When the latter returns 1, we have a hit (a match), otherwise 0, that is, a miss (a no match).

If partial match is required...

=LOOKUP(9.99999999999999E+307,SEARCH($J$26:$L$26,data!E:E),data!E:E)

 

[ouroboros]

Well when I say that they aren't exact I mean that sometimes people will have their shortnames logged as something like Ànna1 but in the system it will be anna1. So the reason that I made it true is because I thought that it would look for something that it close to Ànna1 and find anna1 and say match. Its also the reason for the or statement. I figured that since they had three names if one of them was exact then they it would find it since the three usernames are all slightly different and sometimes only one of them actually match the ones from the dataset.

 

[ouroboros]

None of those worked. Just gave me #N/A. Thanks though.

What I'm really looking for is an if statement that will look up and compare usernames from three different columns to a column of usernames in a different tab that lists sometimes identical but often just similar usernames. If there is a match then I want this if statement to display "Match", otherwise I want it to say "No Match".

 

[etaf]

Letter case is OK - so it will match aaBBBaa with aabbbaa - but wont match if its aabbbbaa

=IF(VLOOKUP(OR(J26,K26,L26),'data'!E:E,5,TRUE)=OR(J26,K26,L26), "Match", "No Match")

=IF( countif(E:E, J26) >0 , "Match", IF( countif(E:E, K26) >0, "Match", IF( countif(E:E, L26) >0, "Match", "No Match")))

OR you could do

=IF( OR( COUNTIF(E:E, J26) >0, COUNTIF(E:E, K26) >0, COUNTIF(E:E, L26) >0),"Match", "No Match")

 

[Aladin Akyurek]

None of those worked. Just gave me #N/A. Thanks though.

What I'm really looking for is an if statement that will look up and compare usernames from three different columns to a column of usernames in a different tab that lists sometimes identical but often just similar usernames. If there is a match then I want this if statement to display "Match", otherwise I want it to say "No Match".

Try to post a sample that illustrates the problem...