Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird behavior of VLOOKUP
I have a VLOOKUP statement that works flawlessly until you get to a certain
place in the table. The VLOOKUP statement is as follows: =IF(ISBLANK(DATABASE!$A82),"",VLOOKUP(DATABASE!$A8 2,JobInfo,1)) I have a range called 'JobInfo' that is defined as =DATABASE!$A$11:$BQ$85 The problem is the result of the VLOOKUP statement gives the wrong information. The VLOOKUP statement is located on a sheet called 'Details' on line 175. Similar VLOOKUP statements start on line 103 of that sheet and go through line 235. The result of the VLOOKUP statement correct through line 174. The results for lines 175 and 176 repeat the same information from line 173. Lines 177 through 235 are blank. When I evaluate the formula, I get the following sequence: IF(ISBLANK(1249),"",VLOOKUP(DATABASE!$A82,JobInfo, 1)) IF(FALSE),"",VLOOKUP(DATABASE!$A82,JobInfo,1)) IF(FALSE),#N/A,VLOOKUP(1249,JobInfo,1)) IF(FALSE),#N/A,VLOOKUP(1249,DATABASE!$A$11:$BQ$85,1)) IF(FALSE),#N/A,1247) 1247 Note that it correctly identifies the contents of A82 as '1249' (which has the offset of 1 in the VLOOKUP table), but when it goes to perform the VLOOKUP, it selects the contents of the cell that is two rows above it. The exact same thing happens with line 176. It chooses the contents from three lines above it. Any clue why this may be happening? If have deleted the range name and recreated it with no success. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird behavior of VLOOKUP
John
is your list in jobInfo definitely sorted on column A? You may be better using the 4 argument version of VLOOKUP (for exact matches), certainly try it to see if it fixes your problem. (add a FALSE after your 1) Or re-sort your JobInfo table and promise to add new entries in the right row cheers Simon "John Simons" wrote: I have a VLOOKUP statement that works flawlessly until you get to a certain place in the table. The VLOOKUP statement is as follows: =IF(ISBLANK(DATABASE!$A82),"",VLOOKUP(DATABASE!$A8 2,JobInfo,1)) I have a range called 'JobInfo' that is defined as =DATABASE!$A$11:$BQ$85 The problem is the result of the VLOOKUP statement gives the wrong information. The VLOOKUP statement is located on a sheet called 'Details' on line 175. Similar VLOOKUP statements start on line 103 of that sheet and go through line 235. The result of the VLOOKUP statement correct through line 174. The results for lines 175 and 176 repeat the same information from line 173. Lines 177 through 235 are blank. When I evaluate the formula, I get the following sequence: IF(ISBLANK(1249),"",VLOOKUP(DATABASE!$A82,JobInfo, 1)) IF(FALSE),"",VLOOKUP(DATABASE!$A82,JobInfo,1)) IF(FALSE),#N/A,VLOOKUP(1249,JobInfo,1)) IF(FALSE),#N/A,VLOOKUP(1249,DATABASE!$A$11:$BQ$85,1)) IF(FALSE),#N/A,1247) 1247 Note that it correctly identifies the contents of A82 as '1249' (which has the offset of 1 in the VLOOKUP table), but when it goes to perform the VLOOKUP, it selects the contents of the cell that is two rows above it. The exact same thing happens with line 176. It chooses the contents from three lines above it. Any clue why this may be happening? If have deleted the range name and recreated it with no success. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird behavior of VLOOKUP
Simon:
After posting my original message, I went back to a previous version of the spreadsheet that 'worked'. I realized that the first column was originally in ascending order, but changes were made to the last two entries that made it out of order. We resorted the table and viola! it works. Thanks for the tip, but we had already figured it out! John "Simon Murphy" wrote: John is your list in jobInfo definitely sorted on column A? You may be better using the 4 argument version of VLOOKUP (for exact matches), certainly try it to see if it fixes your problem. (add a FALSE after your 1) Or re-sort your JobInfo table and promise to add new entries in the right row cheers Simon "John Simons" wrote: I have a VLOOKUP statement that works flawlessly until you get to a certain place in the table. The VLOOKUP statement is as follows: =IF(ISBLANK(DATABASE!$A82),"",VLOOKUP(DATABASE!$A8 2,JobInfo,1)) I have a range called 'JobInfo' that is defined as =DATABASE!$A$11:$BQ$85 The problem is the result of the VLOOKUP statement gives the wrong information. The VLOOKUP statement is located on a sheet called 'Details' on line 175. Similar VLOOKUP statements start on line 103 of that sheet and go through line 235. The result of the VLOOKUP statement correct through line 174. The results for lines 175 and 176 repeat the same information from line 173. Lines 177 through 235 are blank. When I evaluate the formula, I get the following sequence: IF(ISBLANK(1249),"",VLOOKUP(DATABASE!$A82,JobInfo, 1)) IF(FALSE),"",VLOOKUP(DATABASE!$A82,JobInfo,1)) IF(FALSE),#N/A,VLOOKUP(1249,JobInfo,1)) IF(FALSE),#N/A,VLOOKUP(1249,DATABASE!$A$11:$BQ$85,1)) IF(FALSE),#N/A,1247) 1247 Note that it correctly identifies the contents of A82 as '1249' (which has the offset of 1 in the VLOOKUP table), but when it goes to perform the VLOOKUP, it selects the contents of the cell that is two rows above it. The exact same thing happens with line 176. It chooses the contents from three lines above it. Any clue why this may be happening? If have deleted the range name and recreated it with no success. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weird Links Behavior | Links and Linking in Excel | |||
weird macro behavior | Excel Discussion (Misc queries) | |||
Weird worksheet saving behavior | Excel Discussion (Misc queries) | |||
Excel weird behavior | Excel Discussion (Misc queries) | |||
Weird template/macro behavior ?? | Excel Programming |