Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Here's the data that I have on the "Components" sheet (B4:E13):

Component 1        a    Form    Low
Component 2        a    Form    Low
Component 3        a    Form    Medium
Component 4        a    Form    Low
Component 5        a    Form    High
Component 6        a    Form    Medium
Component 7        a    Form    Low
Component 8        a    Form    High
Component 9        a    Form    Medium
Component 10       a    Form    Low

And here's what I have on the "Setup" sheet (F4:G6):

Low         0,4
Medium  1,0
High    1,5

Pretty simple, eh? But wait. Now I have a list of component names on a 3rd sheet, and I want to fetch a corresponding number from the "Setup" sheet. Just a couple of VLOOKUPs (actually, I first tried with LOOKUPs, the result was the same), here's the formula I'm using:

=VLOOKUP(VLOOKUP(B4;  Components!$B$4:$E$23; 4); Setup!$F$4:$G$13; 2)

And the result I'm getting is AMAZING. The formula, simple as it is, works only for two lines out of 10!!! Here's what I'm getting (range B4:C13, with formulas being in C column, referencing component names in the B column):

Component 1 0,4
Component 2 #N/A
Component 3 #N/A
Component 4 #N/A
Component 5 #N/A
Component 6 #N/A
Component 7 #N/A
Component 8 #N/A
Component 9 #N/A
Component 10    0,4

Any idea what the heck is going on here?

share|improve this question

migrated from Jun 1 '11 at 23:51

This question came from our site for professional and enthusiast programmers.

The VLOOKUP function has a fourth argument. Most of the time you want to set it to FALSE, which means only exact matches will do.

share|improve this answer
so true ... it is so weird that fuzzy matching is switched on by default PLUS it is simply not working sensibly – Raffael Nov 20 '13 at 10:06
I've been using Excel for years and only once have I set it to TRUE, when I was matching names I think. – paulmorriss Nov 20 '13 at 10:25

You must log in to answer this question.