VLOOKUP all are RETURNING N/A except three cells

TMAZZ

New Member
Joined
Aug 31, 2014
Messages
10
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-OS779.809275#N/A1A00.12-STD-BBE
A01.00-STD-BBH740.9808#N/A2A00.12-STD-BTC
A04.00-STD-BBE-OS654.88826#N/A3A00.12-XH-BBE
A03.00-STD-BBE-OS477.06246#N/A4A00.25-STD-BBE
A02.00-STD-BBE-OS413.98665#N/A5A00.25-XH-BBE

<tbody>
</tbody>
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
Need to see your lookup and no one can attach files to this forum
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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.
 
Last edited:

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
or =VLOOKUP(TRIM(H14),table3,2,FALSE)
 

TMAZZ

New Member
Joined
Aug 31, 2014
Messages
10
Brillant it worked, this is the first time I came across this can you explain why we have to add &"*"

Thanks so much
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
would seem many of your reference values have additional spaces around them
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,284
Messages
5,467,741
Members
406,550
Latest member
miraclewhip

This Week's Hot Topics

Top