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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com