Problem with using VLookup formula

**mal.b.graham** · 08-26-2012, 04:20 AM

Hi Guys

Problem with using VLookup formula

In a separate tab (List) i have created a table with a breakdown in Col A (i.e Parts, Test, Labour etc) In Col B I have a list of parts etc

I have a table and in Col D I want this to auto populate dependant what is selected in Col E,

so for example if in my main table they select AirBag then this would automatically put (Parts) in Col D, if they select MOT test then this would automatically put (Test) in Col D etc

I have used the following code

Please Login or Register  to view this content.

but this returns #NA in Col D

Any pointers.........

**pmguerra** · 08-26-2012, 05:24 PM

Not quite sure I understand how your data is organized.

Remember the VLOOKUP only looks in the first column of your search range.

**icestationzbra** · 08-26-2012, 07:35 PM

@m.b.g, as the other poster pointed out, your problem could be the way standard VLOOKUP works. there are better ways of fetching data in such cases, one of which is INDEX/MATCH and the other is rearranging data layout. sans both, following is a thread you can read up to figure out how to apply the non-standard approach of VLOOKUP to get desired results for you.

http://www.excelforum.com/july-compe...d-vlookup.html

it looks like you have been around this forum for a while; you must have seen how utterly useful it is when people share sample data rather than just a formula with some explanation that supports that formula, while the main plot (data layout in your case) is shrouded.

**Kevin UK** · 08-27-2012, 04:08 AM

How come we posted in this thread yesterday, with a solution using the index function, I even asked mal.b.graham to post a sample worked sheet, that has even disappeared!!!

The solution was: =Index(List!$A$1:$A$300,Match($E11,List!$B$1:$B$300,0)). I think that was it!!!

I am also sure that Ace_XL posted about 4 minutes after me!

Spooky!!

**Cutter** · 08-27-2012, 09:31 AM

There were problems with the site yesterday. Everything I was able to edit or post during the day was gone by last evening and had to be redone.

**Kevin UK** · 08-27-2012, 09:46 AM

Hi icestationzbra

To be fair, he did post a worksheet after I requested, it has vanished due to the problems that the Forum was experiencing yesterday. I downloaded it and responded to mal.b.graham

Originally Posted by icestationzbra

@m.b.g, as the other poster pointed out, your problem could be the way standard VLOOKUP works. there are better ways of fetching data in such cases, one of which is INDEX/MATCH and the other is rearranging data layout. sans both, following is a thread you can read up to figure out how to apply the non-standard approach of VLOOKUP to get desired results for you.

http://www.excelforum.com/july-compe...d-vlookup.html

it looks like you have been around this forum for a while; you must have seen how utterly useful it is when people share sample data rather than just a formula with some explanation that supports that formula, while the main plot (data layout in your case) is shrouded.