Searching for data in two lists player a & player b the same as player b & player a

[TerrorTot38]

I have two lists of data one has all the pairings (column A) one has my pairings for the next week (column B).

I am using vlookup

=VLOOKUP(B2,$A$2:$A$191,1,FALSE)

to compare the two lists which is working correctly as its showing the names that appear the way in the search list. However some pairings do not appear for example Player A & Player B but in my search list its Player B & Player A.

Is there a function / formula that will allow for both as I have tried a wild card to no avail.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is this what you mean?

22 02 03.xlsm
	A	B	C
1
2	Player A & Player B	Player C & Player A	Player A & Player C
3	Player A & Player C	Player A & Player D	Player A & Player D
4	Player A & Player D	Player C & Player D	#N/A
Match Pairs

Cell Formulas
Range	Formula
C2:C4	C2	=IFNA(VLOOKUP(B2,$A$2:$A$191,1,FALSE),VLOOKUP(MID(B2&" & "&B2,FIND("&",B2)+2,LEN(B2)),$A$2:$A$191,1,FALSE))

 

[TerrorTot38]

Hi, yes sorry I only have 365 and LibreOffice so could not get the extension to work.

Thank you very much it is working perfectly. If the value doesn't exist at all is there a way for it to show something other that #N/A?

Thanks again,

 

[Peter_SSs]

If the value doesn't exist at all is there a way for it to show something other that #N/A?

Sure, just use another IFNA

=IFNA(IFNA(VLOOKUP(B2,$A$2:$A$191,1,FALSE),VLOOKUP(MID(B2&" & "&B2,FIND("&",B2)+2,LEN(B2)),$A$2:$A$191,1,FALSE)),"Not found")

 

[TerrorTot38]

Sure, just use another IFNA

=IFNA(IFNA(VLOOKUP(B2,$A$2:$A$191,1,FALSE),VLOOKUP(MID(B2&" & "&B2,FIND("&",B2)+2,LEN(B2)),$A$2:$A$191,1,FALSE)),"Not found")

Thank you soo much this is working perfectly with my nulled data set and proper data set :D Really appreciate your help with this.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[TerrorTot38]

You're welcome. Thanks for the follow-up.

No problem at all. My pleasure