asked on

#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

ASKER CERTIFIED SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

patrickab

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

Patrick

SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KnutsonBM

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

Cluskitt

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.

Cluskitt

Err, I meant name2 :P

SOLUTION

KnutsonBM

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KnutsonBM

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

SOLUTION