VLOOKUP all are RETURNING N/A except three cells

TMAZZ

New Member
Joined
Aug 31, 2014
Messages
12
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:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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,203
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,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

TMAZZ

New Member
Joined
Aug 31, 2014
Messages
12
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,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
would seem many of your reference values have additional spaces around them
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top