?
Solved

#N/A in vlookup

Posted on 2011-02-24
9
Medium Priority
?
299 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 500 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 500 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 500 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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
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…
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…

718 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