VLOOKUP all are RETURNING N/A except three cells

[TMAZZ]

See below, the first two columns - I called it table 3

• I created a vlookup where 98% returned with a #N/A except for three cells?
• Can you please help me understand why this is happening?

Copy and paste into your excel - the format is the same acrosss the board?

Also why can't I upload an excel file into this board anymore? It would be alot easier to explain and see what the issue is?

				#	Item No
A06.00-STD-BBE-OS	779.809275		#N/A	1	A00.12-STD-BBE
A01.00-STD-BBH	740.9808		#N/A	2	A00.12-STD-BTC
A04.00-STD-BBE-OS	654.88826		#N/A	3	A00.12-XH-BBE
A03.00-STD-BBE-OS	477.06246		#N/A	4	A00.25-STD-BBE
A02.00-STD-BBE-OS	413.98665		#N/A	5	A00.25-XH-BBE

<tbody>
</tbody>

 

[mole999]

Need to see your lookup and no one can attach files to this forum

 

[TMAZZ]

=VLOOKUP(H14,table3,2,FALSE)

 

[Aladin Akyurek]

Does the following wotk for you?

=VLOOKUP(H14&"*",table3,2,FALSE)

If not, try to post the results which must obtain instead of the formula results you have.

 

[mole999]

or =VLOOKUP(TRIM(H14),table3,2,FALSE)

 

[TMAZZ]

Brillant it worked, this is the first time I came across this can you explain why we have to add &"*"

Thanks so much

 

[mole999]

would seem many of your reference values have additional spaces around them

 

[Aladin Akyurek]

Brillant it worked, this is the first time I came across this can you explain why we have to add &"*"

Thanks so much

The wildcard stands for zero or more chars (including digits).


=VLOOKUP(H14&"*",table3,2,FALSE)

will succeed if (a) H14 is available either as is or partially in the match column of table3.

Lookup value A00.50-STD-BBE followed by * (done with &"*") will match for example:

A00.50-STD-BBE
A00.50-STD-BBE followed by a space
A00.50-STD-BBEXT
etc.