If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem working with Vlookup
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 |
#2
|
|||
|
|||
Problem working with Vlookup
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 On Sat, 16 May 2009 11:07:01 -0700, "Patrick C. Simonds" wrote: 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 |
#3
|
|||
|
|||
Problem working with Vlookup
Try it like this:
=INDEX('Sunday (3)'!A1:A6,MATCH(R16,'Sunday (3)'!B1:B6,0)) -- Biff Microsoft Excel MVP "Patrick C. Simonds" wrote in message ... 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 |
#4
|
|||
|
|||
Problem working with Vlookup
In article ,
"Patrick C. Simonds" wrote: 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)) -- Domenic http://www.xl-central.com |
Thread Tools | |
Display Modes | |
|
|