| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup error?
ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12! If i were to put a number 12 instead of "INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works as intended. in other words if i were to just type in =INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value of 12, so why does it not work on vlookup. another thing i have tried is to put =INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will return a value of 12 and then i would link this cell to the vlookup fomula like below: =VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5" returns a value of 12, the vlookup function does not work it gives a #N/A. may this be because Vlookup cannot nest to many references?? Thanks in advance TG |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup error?
The LEFT function returns a TEXT value.
A1 = 12345 (a numeric value) =LEFT(A1,2) returns the TEXT value 12. That is not the same as the numeric value 12. VLOOKUP doesn't evaluate TEXT numbers and numeric numbers as being equal so you get #N/A. Add 2 dashes in front of the LEFT function like this: =VLOOKUP(INDEX(--LEFT(reference!G3:G7,2),reference!G9),Brackets!A2: F5,6) The dashes, known as a double unary minus, will coerce the TEXT number into a numeric number. -- Biff Microsoft Excel MVP "TG" wrote in message ... ok so my problem is when using =VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12! If i were to put a number 12 instead of "INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works as intended. in other words if i were to just type in =INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value of 12, so why does it not work on vlookup. another thing i have tried is to put =INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will return a value of 12 and then i would link this cell to the vlookup fomula like below: =VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5" returns a value of 12, the vlookup function does not work it gives a #N/A. may this be because Vlookup cannot nest to many references?? Thanks in advance TG |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup error?
I am assuming that 12 is a number and not text...
LEFT(reference!G3:G7,2) returns a string not a number. This may be the source of your problem. Put =ISNUMBER(INDEX(LEFT(reference!G3:G7,2),reference! G9)) in a cell and check To convert to number you can multiply by 1 and then pass to VLOOKUP. "TG" wrote: ok so my problem is when using =VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12! If i were to put a number 12 instead of "INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works as intended. in other words if i were to just type in =INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value of 12, so why does it not work on vlookup. another thing i have tried is to put =INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will return a value of 12 and then i would link this cell to the vlookup fomula like below: =VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5" returns a value of 12, the vlookup function does not work it gives a #N/A. may this be because Vlookup cannot nest to many references?? Thanks in advance TG |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup error?
Hi,
Try =VLOOKUP(VALUE(INDEX(LEFT(reference!G3:G7,2),refer ence!G9)),Brackets!A2:F5,6) If this helps, please click the Yes button Cheers, Shane Devenshire "TG" wrote in message ... ok so my problem is when using =VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12! If i were to put a number 12 instead of "INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works as intended. in other words if i were to just type in =INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value of 12, so why does it not work on vlookup. another thing i have tried is to put =INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will return a value of 12 and then i would link this cell to the vlookup fomula like below: =VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5" returns a value of 12, the vlookup function does not work it gives a #N/A. may this be because Vlookup cannot nest to many references?? Thanks in advance TG |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup error?
Thank you very much, this works really well!
you guys are great! "Sheeloo" wrote: I am assuming that 12 is a number and not text... LEFT(reference!G3:G7,2) returns a string not a number. This may be the source of your problem. Put =ISNUMBER(INDEX(LEFT(reference!G3:G7,2),reference! G9)) in a cell and check To convert to number you can multiply by 1 and then pass to VLOOKUP. "TG" wrote: ok so my problem is when using =VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12! If i were to put a number 12 instead of "INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works as intended. in other words if i were to just type in =INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value of 12, so why does it not work on vlookup. another thing i have tried is to put =INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will return a value of 12 and then i would link this cell to the vlookup fomula like below: =VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5" returns a value of 12, the vlookup function does not work it gives a #N/A. may this be because Vlookup cannot nest to many references?? Thanks in advance TG |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup error?
Thank you very much, now I know of 2 ways to go about this problem.
Am really glad microsoft has not yet made this resource "pay per answer"! "T. Valko" wrote: The LEFT function returns a TEXT value. A1 = 12345 (a numeric value) =LEFT(A1,2) returns the TEXT value 12. That is not the same as the numeric value 12. VLOOKUP doesn't evaluate TEXT numbers and numeric numbers as being equal so you get #N/A. Add 2 dashes in front of the LEFT function like this: =VLOOKUP(INDEX(--LEFT(reference!G3:G7,2),reference!G9),Brackets!A2: F5,6) The dashes, known as a double unary minus, will coerce the TEXT number into a numeric number. -- Biff Microsoft Excel MVP "TG" wrote in message ... ok so my problem is when using =VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12! If i were to put a number 12 instead of "INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works as intended. in other words if i were to just type in =INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value of 12, so why does it not work on vlookup. another thing i have tried is to put =INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will return a value of 12 and then i would link this cell to the vlookup fomula like below: =VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5" returns a value of 12, the vlookup function does not work it gives a #N/A. may this be because Vlookup cannot nest to many references?? Thanks in advance TG |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup error?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "TG" wrote in message ... Thank you very much, now I know of 2 ways to go about this problem. Am really glad microsoft has not yet made this resource "pay per answer"! "T. Valko" wrote: The LEFT function returns a TEXT value. A1 = 12345 (a numeric value) =LEFT(A1,2) returns the TEXT value 12. That is not the same as the numeric value 12. VLOOKUP doesn't evaluate TEXT numbers and numeric numbers as being equal so you get #N/A. Add 2 dashes in front of the LEFT function like this: =VLOOKUP(INDEX(--LEFT(reference!G3:G7,2),reference!G9),Brackets!A2: F5,6) The dashes, known as a double unary minus, will coerce the TEXT number into a numeric number. -- Biff Microsoft Excel MVP "TG" wrote in message ... ok so my problem is when using =VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12! If i were to put a number 12 instead of "INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works as intended. in other words if i were to just type in =INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value of 12, so why does it not work on vlookup. another thing i have tried is to put =INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will return a value of 12 and then i would link this cell to the vlookup fomula like below: =VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5" returns a value of 12, the vlookup function does not work it gives a #N/A. may this be because Vlookup cannot nest to many references?? Thanks in advance TG |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP #N/A Error | Excel Worksheet Functions | |||
| vlookup error | Excel Discussion (Misc queries) | |||
| Vlookup value not available error | Excel Discussion (Misc queries) | |||
| VLOOKUP error in VBA | Excel Discussion (Misc queries) | |||
| VLOOKUP error | Excel Worksheet Functions | |||
Linear Mode
