# VLOOKUP = #N/A error

#### MeCraig

##### New Member
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### RoryA

##### MrExcel MVP, Moderator
What's the formula, and what's the item?

#### MeCraig

##### New Member
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 Bol 1052 Turkey Pasta 935 Pasta and Sauce 754

#### Dave Patton

##### Well-known Member
try
=VLOOKUP(E14,'Pasta Night'!G3:H5,2,0) For exact match. The data is in random order.

#### Fluff

##### MrExcel MVP, Moderator

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)``

#### Trixterz

##### Board Regular
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

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
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
If the first column is sorted A-Z then you wouldn't have a problem, it's only when you have un-sorted data.

Replies
7
Views
241
Replies
5
Views
182
Replies
2
Views
332
Replies
0
Views
508
Replies
1
Views
75

1,147,735
Messages
5,742,869
Members
423,760
Latest member
photogfrog

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

### Which adblocker are you using?

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

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