#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
chaitu chaituAsked:
Who is Participating?
 
CluskittConnect With a Mentor 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
 
patrickabCommented:
The 'Contracted' codes need to be in the first column of the range used by VLOOKUP().

Patrick
0
 
patrickabConnect With a Mentor 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
KnutsonBMCommented:
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
 
CluskittCommented:
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
 
CluskittCommented:
Err, I meant name2 :P
0
 
KnutsonBMConnect With a Mentor Commented:
whoops:

=index($F$1:$G$9,Match($A2,$H$2:$H$9,0),Match(B$1,$F$1:$H$1,0))
0
 
KnutsonBMCommented:
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
 
barry houdiniConnect With a Mentor 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
All Courses

From novice to tech pro — start learning today.