Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup not working | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Discussion (Misc queries) | |||
Vlookup not working | Excel Discussion (Misc queries) | |||
VLOOKUP not working | Excel Discussion (Misc queries) | |||
VLOOKUP not working??? | Excel Worksheet Functions |