VLOOKUP = #N/A error

[MeCraig]

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?

 

[RoryA]

What's the formula, and what's the item?

 

[MeCraig]

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]

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

 

[Fluff]

You haven't included the last argument, so it's trying to do an approximate match. Try it like

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

 

[Trixterz]

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.

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

 

[Fluff]

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]

You haven't included the last argument, so it's trying to do an approximate match. Try it like

=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]

If the first column is sorted A-Z then you wouldn't have a problem, it's only when you have un-sorted data.