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!$A82,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.

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!$A82,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.
>

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!$A82,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.
> >