Lookup cell value in array, VLOOKUP results #N/A

[Nanaia]

I'm searching a column of numbers for a value that is the result of a TEXTJOIN formula. The result of the TEXTJOIN is a number (e.g. 1234). The column of numbers are simply data input numbers. When I search the column for the result of the TEXTJOIN I keep getting #N/A. I know there's a way to use VLOOKUP or INDEX but I can't get any to work.

The value in F2 is a TEXTJOIN result of 1111. I'm searching column H:H for it and want it to say YES if found, NO if not found. The formula used is [ VLOOKUP(F2,H:H,1,0)] and it only gives the result #N/A.

 

[Fluff]

If the values in col H are numbers & not text, try

VLOOKUP(F2+0,H:H,1,0)

 

[Nanaia]

I found that suggestion right as you were responding. Thank you! It works.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Norie]

TEXTJOIN returns text, so for your VLOOKUP or INDEX/MATCH to work you would either need to be looking for a string or convert the result of the TEXTJOIN to a number.

 

[Nanaia]

So if the letter N is added to the end of the TEXTJOIN result, how would you change the formula since it's no longer looking for just numbers? VLOOKUP gives a #VALUE! error. IF was with a YES, NO result was added to the VLOOKUP. [=IF(VLOOKUP(Q2,S:S,1,FALSE),"YES","NO")]

 

[Fluff]

Ideally col S should contain either numbers, or text & not a mix of both.