Solved

#N/A in vlookup

Posted on 2011-02-24
9
288 Views
Last Modified: 2012-05-11

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
Comment
Question by:chaitu chaitu
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 18

Accepted Solution

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

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

Patrick
0
 
LVL 45

Assisted Solution

by:patrickab
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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 6

Expert Comment

by:KnutsonBM
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

by:Cluskitt
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

by:Cluskitt
ID: 34969256
Err, I meant name2 :P
0
 
LVL 6

Assisted Solution

by:KnutsonBM
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

by:KnutsonBM
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

by:barry houdini
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question