CODEWITHSUNDEEP
excelvlookup
DomeAlonso
DomeAlonso

Reputation: 15

Excel VLookup #NV error

I'm trying to make a VLookup in Excel but I get everytime a #NV error.

This is table EVENTS:

EVENTS

This is table TRACK:

TRACK

the formula on field F2 in table EVENTS is 

=SVERWEIS(E2;TRACKS!$A$2:$B$52;1;FALSCH)

SVERWEIS is the word for VLOOKUP in the German version. FALSCH means wrong

Upvotes: 0

Views: 2633

Answers (3)

Marek Stejskal
Marek Stejskal

Reputation: 2708

VLOOKUP compares the values in the first column of your reference target, you have your target values in the second.

Just swap VLOOKUP and the TEXT columns on your TRACKS sheet and it will work just fine.

Upvotes: 1

pnuts
pnuts

Reputation: 59485

As has been mentioned, VLOOKUP (SVERWEIS) can only look to its right to find a value to correspond with a value in the left hand columns of a table. The INDEX/MATCH combination is more flexible in this respect so if not to rearrange your columns I would suggest something like: 

=INDEX(A:A,MATCH(E2,B:B,0))

where TRACK is assumed to be in ColumnsA:B. Converting to German, perhaps: 

=INDEX(TRACKS!A:A;VERGLEICH(E2;TRACKS!B:B;0))

Upvotes: 1

SCB
SCB

Reputation: 6149

Try switching the columns in TRACKS around.

VLOOKUP bases it's lookup on the first column, so in your case, it's looking through column A (1, 2, 3, etc.)

If you want your VLOOKUP to be based on the text, it needs to be in A instead.

i.e.

  | A      | B       |
----------------------
1 | TEXT   | VLOOKUP |
2 | Text1  | 1       |
3 | Text2  | 2       |
etc...

Then your function will be:

=SVERWEIS(E2;TRACKS!$A$2:$B$52;2;FALSCH)

Switching out the third argument because you now want the value from the second column

Upvotes: 0

Related Questions