Solved

#N/A in vlookup

Posted on 2011-02-24
9
296 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

627 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