Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
Excel 2000
Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
Did you check for extra blanks at the end of your values?
HTH -- AP "amberlodge" a écrit dans le message de ... Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
The usual suspects:
Leading or trailing spaces in the lookup column. Does the named range contain ALL the data? Use the 4th argument as False or 0 =VLOOKUP(E2,Range,2,0) HTH Regards, Howard "amberlodge" wrote in message ... Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
I just did and could find no extra spaces or blanks. Several names have the
same homeroom; with some the formula works, with others it doesn't. If I paste the lookup value that works into the ones that don't, the formula works. Yet the cells were identical. I tried typing the number in from scratch and that did not work. "Ardus Petus" wrote: Did you check for extra blanks at the end of your values? HTH -- AP "amberlodge" a écrit dans le message de ... Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
I checked for spaces - there were none that I could find. I tried typing in
the data from scratch - no good. Copying and pasting from those cells that did work into the (identical) cells that didn't, caused the formula to return the correct value. But I still have homerooms that have never given a correct value. Even true, false, and 0 don't help in the fourth argument. The range contains all the data - it's not very big - just 44 rows. "L. Howard Kittle" wrote: The usual suspects: Leading or trailing spaces in the lookup column. Does the named range contain ALL the data? Use the 4th argument as False or 0 =VLOOKUP(E2,Range,2,0) HTH Regards, Howard "amberlodge" wrote in message ... Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
Change the lookup formula to
=VLOOKUP(E2,Range,2,0) if you still have the same problem and if there are no invisible characters then there must be numbers involved where one set are seen as number and one as text, just do this =ISTEXT(Cell1) =ISTEXT(Cell2) where the first one is the lookup value cell and the second one cell in the table that returns an error if you get TRUE in one of these (probably the second) copy an empty cell, select all the room numbers and do editpaste special and select add -- Regards, Peo Sjoblom http://nwexcelsolutions.com "amberlodge" wrote in message ... I just did and could find no extra spaces or blanks. Several names have the same homeroom; with some the formula works, with others it doesn't. If I paste the lookup value that works into the ones that don't, the formula works. Yet the cells were identical. I tried typing the number in from scratch and that did not work. "Ardus Petus" wrote: Did you check for extra blanks at the end of your values? HTH -- AP "amberlodge" a écrit dans le message de ... Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
Thanks for your replies. I did try 0 in the fourth argument. No good. But I
was checking for spaces and clicked in front of several of the homeroom values in my table, which caused them to be right-justified instead of left (thus changing the format?) and the formulas that were working gave values of #N/A. I left-justified them again and now the entire dtatbase is fine. I have no idea what I did to clear up the problem, but I all the variations of the formula that I was trying work now. "Peo Sjoblom" wrote: Change the lookup formula to =VLOOKUP(E2,Range,2,0) if you still have the same problem and if there are no invisible characters then there must be numbers involved where one set are seen as number and one as text, just do this =ISTEXT(Cell1) =ISTEXT(Cell2) where the first one is the lookup value cell and the second one cell in the table that returns an error if you get TRUE in one of these (probably the second) copy an empty cell, select all the room numbers and do editpaste special and select add -- Regards, Peo Sjoblom http://nwexcelsolutions.com "amberlodge" wrote in message ... I just did and could find no extra spaces or blanks. Several names have the same homeroom; with some the formula works, with others it doesn't. If I paste the lookup value that works into the ones that don't, the formula works. Yet the cells were identical. I tried typing the number in from scratch and that did not work. "Ardus Petus" wrote: Did you check for extra blanks at the end of your values? HTH -- AP "amberlodge" a écrit dans le message de ... Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
After reading the messages back & forth I'm wondering if the name "Range"
uses absolute or relative addresses. Go to InsertNamesDefine and make sure the definition has $ signs in the addresses "amberlodge" wrote: Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
If they are left aligned they are text
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Nothwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "amberlodge" wrote in message ... Thanks for your replies. I did try 0 in the fourth argument. No good. But I was checking for spaces and clicked in front of several of the homeroom values in my table, which caused them to be right-justified instead of left (thus changing the format?) and the formulas that were working gave values of #N/A. I left-justified them again and now the entire dtatbase is fine. I have no idea what I did to clear up the problem, but I all the variations of the formula that I was trying work now. "Peo Sjoblom" wrote: Change the lookup formula to =VLOOKUP(E2,Range,2,0) if you still have the same problem and if there are no invisible characters then there must be numbers involved where one set are seen as number and one as text, just do this =ISTEXT(Cell1) =ISTEXT(Cell2) where the first one is the lookup value cell and the second one cell in the table that returns an error if you get TRUE in one of these (probably the second) copy an empty cell, select all the room numbers and do editpaste special and select add -- Regards, Peo Sjoblom http://nwexcelsolutions.com "amberlodge" wrote in message ... I just did and could find no extra spaces or blanks. Several names have the same homeroom; with some the formula works, with others it doesn't. If I paste the lookup value that works into the ones that don't, the formula works. Yet the cells were identical. I tried typing the number in from scratch and that did not work. "Ardus Petus" wrote: Did you check for extra blanks at the end of your values? HTH -- AP "amberlodge" a écrit dans le message de ... Excel 2000 Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range") =VLOOKUP(E2,Range,2) Some of the cells return the correct name, others #N/A. As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either. Any ideas? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP works in some cells but not others.
Maybe this.. =VLOOKUP(E2,Range,2,FALSE) -- Infinity ------------------------------------------------------------------------ Infinity's Profile: http://www.excelforum.com/member.php...o&userid=32725 View this thread: http://www.excelforum.com/showthread...hreadid=532711 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) | |||
getting data from cells in wrksheet & importing in existing works | Excel Discussion (Misc queries) | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |