Solved

#N/A in vlookup

Posted on 2011-02-24
9
291 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
[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
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…

710 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