# #N/A in vlookup

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

LVL 20
###### Who is Participating?

Commented:
#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

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

Patrick
0

Commented:
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

Commented:
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

Commented:
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

Commented:
Err, I meant name2 :P
0

Commented:
whoops:

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

Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.