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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
What's the formula, and what's the item?
 
Upvote 0
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
 
Upvote 0
try
=VLOOKUP(E14,'Pasta Night'!G3:H5,2,0) For exact match. The data is in random order.
 
Upvote 0
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)
 
Upvote 0
Solution
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)
 
Upvote 0
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. ;)
 
Upvote 0
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
 
Upvote 0
If the first column is sorted A-Z then you wouldn't have a problem, it's only when you have un-sorted data.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top