VLOOKUP = #N/A error

MeCraig

New Member
Joined
Nov 2, 2016
Messages
6
Hi all,

I've got a super simple VLOOKUP for a diet plan I'm working on with my partner.

For the life of me though, I can't understand why the #N/A error is occurring.

I'm using a Data Validation list that is directly linked to the source table so even if there were hidden spaces it should still pull through the correct amount.

The issue is only occurring on one of the items in the Drop Down List...I've checked for spaces, Copied and Pasted the formatting etc...

What am I missing?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,550
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What's the formula, and what's the item?
 

MeCraig

New Member
Joined
Nov 2, 2016
Messages
6
What's the formula, and what's the item?
The below formula run across the below table based on the value that it's in E14 on sheet1 (the Data Validation list is run across the table)
As I've said, the value for 'Pasta and Sauce' is the ONLY one in the table that returns an error and I've done everything I can think of.

=VLOOKUP(E14,'Pasta Night'!G3:H5,2)

Spag Bol1052
Turkey Pasta935
Pasta and Sauce754
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,959
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
try
=VLOOKUP(E14,'Pasta Night'!G3:H5,2,0) For exact match. The data is in random order.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,612
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You haven't included the last argument, so it's trying to do an approximate match. Try it like
Excel Formula:
=VLOOKUP(E14,'Pasta Night'!G3:H5,2,0)
 
Solution

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
85
Due to VLookUp limitation you need to rearrange your table where the first column of list in your table is what you wish for VLookUp to search for and every columns after that can be whatever you want VLookUp to retrieve from the table.

Excel Formula:
=VLOOKUP(E14,'Pasta Night'!G3:H5,2,TRUE)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,612
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Due to VLookUp limitation you need to rearrange your table where the first column of list in your table is what you wish for VLookUp to search for and every columns after that can be whatever you want VLookUp to retrieve from the table.
The OP has already done that. ;)
 

MeCraig

New Member
Joined
Nov 2, 2016
Messages
6
You haven't included the last argument, so it's trying to do an approximate match. Try it like
Excel Formula:
=VLOOKUP(E14,'Pasta Night'!G3:H5,2,0)
This worked. I've used a lot of VLOOKUPs before and never bothered with the final part.

Not come across this before
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,612
Office Version
  1. 365
Platform
  1. Windows
If the first column is sorted A-Z then you wouldn't have a problem, it's only when you have un-sorted data.
 

Forum statistics

Threads
1,143,621
Messages
5,719,795
Members
422,244
Latest member
AYSHANA

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