Problem working with Vlookup

P

Patrick C. Simonds

I have the following table (A1:B6) from which I want to return the
appropriate value from column 1. This is my formula:

VLOOKUP(R16,'Sunday (3)'!A1:B6,1,TRUE)

My problem is that if R16 contains AIL, Floating Holiday, LWOP, Regular
Holiday the formula returns #NA, Sick returns NW6 not NW5 and Vacation
returns the correct value NW6.

As a test I tried swapping Vacation and LWOP and but Vacation Still returned
the NW6 value.


A B
1 NW1 AIL
2 NW2 Floating Holiday
3 NW3 LWOP
4 NW4 Regular Holiday
5 NW5 Sick
6 NW6 Vacation
 
G

Gord Dibben

I'm surprised you get any return other than an error with that construct.

VLOOKUP won't look left.

Swap columns A and B then =VLOOKUP(R16,'Sunday (3)'!$A$1:$B$6,2,TRUE)


Gord Dibben MS Excel MVP
 
D

Domenic

Patrick C. Simonds said:
I have the following table (A1:B6) from which I want to return the
appropriate value from column 1. This is my formula:

VLOOKUP(R16,'Sunday (3)'!A1:B6,1,TRUE)

My problem is that if R16 contains AIL, Floating Holiday, LWOP, Regular
Holiday the formula returns #NA, Sick returns NW6 not NW5 and Vacation
returns the correct value NW6.

As a test I tried swapping Vacation and LWOP and but Vacation Still returned
the NW6 value.


A B
1 NW1 AIL
2 NW2 Floating Holiday
3 NW3 LWOP
4 NW4 Regular Holiday
5 NW5 Sick
6 NW6 Vacation


Try...

=INDEX('Sunday (3)'!A1:A6,MATCH(R16,'Sunday (3)'!B1:B6,0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top