Solved

# #N/A in vlookup

Posted on 2011-02-24
291 Views

EMPID column i put this formula but getting #N/A

=VLOOKUP(A2,F2:H9,1,FALSE)

CONTRACTID      EMPID
C11      #N/A
C12
C13
C14
C15
C16
C17
C18

EMPID      NAME      CONTRACTID
1      BVDG      C13
2      WRWR      C16
3      SFWERW      C14
4      DGD      C18
5      SDG      C12
6      WRWR      C11
7      GDG      C15
8      WRWR      C17

0
Question by:chaitu chaitu
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2
• +1

LVL 18

Accepted Solution

Cluskitt earned 125 total points
ID: 34969241
#N/A means that there's no match. Remember that the column that it will check will be F, not H. VLOOKUP is always left to right (unless you work some very complicated formulas to reverse it).
0

LVL 45

Expert Comment

ID: 34969247
The 'Contracted' codes need to be in the first column of the range used by VLOOKUP().

Patrick
0

LVL 45

Assisted Solution

patrickab earned 125 total points
ID: 34969251
Like this:

Â Â Â Â Â CONTRACTID      EMPID      Â Â Â Â Â NAME
Â Â Â Â Â C13      1      Â Â Â Â Â BVDG
Â Â Â Â Â C16      2      Â Â Â Â Â WRWR
Â Â Â Â Â C14      3      Â Â Â Â Â SFWERW
Â Â Â Â Â C18      4      Â Â Â Â Â DGD
Â Â Â Â Â C12      5      Â Â Â Â Â SDG
Â Â Â Â Â C11      6      Â Â Â Â Â WRWR
Â Â Â Â Â C15      7      Â Â Â Â Â GDG
Â Â Â Â Â C17      8      Â Â Â Â Â WRWR
0

LVL 6

Expert Comment

ID: 34969252
this would be a good spot to use Index/Match i believe

Try this:

=index(\$F\$1:\$G:\$9,Match(\$A2,\$H\$2:\$H\$9,0),Match(B\$1,\$F\$1:\$H\$1,0))
0

LVL 18

Expert Comment

ID: 34969255
If you need it to be that order, one easy fix is to add 2 columns that simply replicate the left ones:

EMPID      NAME      CONTRACTID   EMPID2    CONTRACTID2
1               BVDG       C13                   1               BVDG

You can then create a vlookup for the last 3.
0

LVL 18

Expert Comment

ID: 34969256
Err, I meant name2 :P
0

LVL 6

Assisted Solution

KnutsonBM earned 125 total points
ID: 34969260
whoops:

=index(\$F\$1:\$G\$9,Match(\$A2,\$H\$2:\$H\$9,0),Match(B\$1,\$F\$1:\$H\$1,0))
0

LVL 6

Expert Comment

ID: 34969279
This is more difficult without the spreadsheet:

=index(\$F\$2:\$G\$9,Match(\$A2,\$H\$2:\$H\$9,0),Match(B\$1,\$F\$1:\$H\$1,0))
0

LVL 50

Assisted Solution

barry houdini earned 125 total points
ID: 34970220
INDEX/MATCH is the way to go, I believe, assuming that you know that you want to match in H and return a value from F then just this formula in B2 copied down

=INDEX(F\$2:F\$9,MATCH(A2,H\$2:H\$9,0))

regards, barry

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference texâ€¦
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month2 days, left to enroll