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?
[TABLE="width: 888"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD]Item No[/TD]
[/TR]
[TR]
[TD]A06.00-STD-BBE-OS[/TD]
[TD]779.809275[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]1[/TD]
[TD]A00.12-STD-BBE[/TD]
[/TR]
[TR]
[TD]A01.00-STD-BBH[/TD]
[TD]740.9808[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]2[/TD]
[TD]A00.12-STD-BTC[/TD]
[/TR]
[TR]
[TD]A04.00-STD-BBE-OS[/TD]
[TD]654.88826[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]3[/TD]
[TD]A00.12-XH-BBE[/TD]
[/TR]
[TR]
[TD]A03.00-STD-BBE-OS[/TD]
[TD]477.06246[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]4[/TD]
[TD]A00.25-STD-BBE[/TD]
[/TR]
[TR]
[TD]A02.00-STD-BBE-OS[/TD]
[TD]413.98665[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]5[/TD]
[TD]A00.25-XH-BBE[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.