ExcelBanter - LOOKUP is delivering inaccurate data

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - LOOKUP is delivering inaccurate data (https://www.excelbanter.com/excel-worksheet-functions/107558-lookup-delivering-inaccurate-data.html)

LOOKUP is delivering inaccurate data

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom

LOOKUP is delivering inaccurate data

Select the cell
Press the F5 key
Click on Special
Click on 'Dependents'
Click on OK

"Tom" wrote:

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom

LOOKUP is delivering inaccurate data

That worked fine, but I still can't solve the underlying problem.

The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Line 270, Column B should display what is in B171, but it isn't. Instead it
is going up the sheet and displaying information that isn't asked for in the
formula.

FYI: Line
146| 139 7/21/06 5.92

A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

Any suggestions?

Thanks,

Tom

"Duke Carey" wrote:

Select the cell
Press the F5 key
Click on Special
Click on 'Dependents'
Click on OK

"Tom" wrote:

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom

LOOKUP is delivering inaccurate data

"Tom" wrote:
.. The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Believe the lookup vector range: BG10:BG$267
needs to be sorted in ascending order
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

LOOKUP is delivering inaccurate data

From the Help file LOOKUP()
---------------------------------------------------------------
Lookup_vector is a range that contains only one row or one column. The
values in lookup_vector can be text, numbers, or logical values.

Important The values in lookup_vector must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value. Uppercase and lowercase text are equivalent.
---------------------------------------------------------------

So..you gotta sort BG in ascending order

"Tom" wrote:

That worked fine, but I still can't solve the underlying problem.

The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Line 270, Column B should display what is in B171, but it isn't. Instead it
is going up the sheet and displaying information that isn't asked for in the
formula.

FYI: Line
146| 139 7/21/06 5.92

A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

Any suggestions?

Thanks,

Tom

"Duke Carey" wrote:

Select the cell
Press the F5 key
Click on Special
Click on 'Dependents'
Click on OK

"Tom" wrote:

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom

LOOKUP is delivering inaccurate data

BG is chronological data inputed based off of B...the current date.

The formula worked fine before I cut 1/2 of the data and pasted it to
another worksheet within the workbook.

I'm stumped.

Tom

"Duke Carey" wrote:

From the Help file LOOKUP()
---------------------------------------------------------------
Lookup_vector is a range that contains only one row or one column. The
values in lookup_vector can be text, numbers, or logical values.

Important The values in lookup_vector must be placed in ascending order:
...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value. Uppercase and lowercase text are equivalent.
---------------------------------------------------------------

So..you gotta sort BG in ascending order

"Tom" wrote:

That worked fine, but I still can't solve the underlying problem.

The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Line 270, Column B should display what is in B171, but it isn't. Instead it
is going up the sheet and displaying information that isn't asked for in the
formula.

FYI: Line
146| 139 7/21/06 5.92

A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

Any suggestions?

Thanks,

Tom

"Duke Carey" wrote:

Select the cell
Press the F5 key
Click on Special
Click on 'Dependents'
Click on OK

"Tom" wrote:

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom

LOOKUP is delivering inaccurate data

Unless your example data is wrong, column BG really IS NOT SORTED

FYI: Line
146| 139 7/21/06 5.92

A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

"Tom" wrote:

BG is chronological data inputed based off of B...the current date.

The formula worked fine before I cut 1/2 of the data and pasted it to
another worksheet within the workbook.

I'm stumped.

Tom

"Duke Carey" wrote:

From the Help file LOOKUP()
---------------------------------------------------------------
Lookup_vector is a range that contains only one row or one column. The
values in lookup_vector can be text, numbers, or logical values.

Important The values in lookup_vector must be placed in ascending order:
...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value. Uppercase and lowercase text are equivalent.
---------------------------------------------------------------

So..you gotta sort BG in ascending order

"Tom" wrote:

That worked fine, but I still can't solve the underlying problem.

The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Line 270, Column B should display what is in B171, but it isn't. Instead it
is going up the sheet and displaying information that isn't asked for in the
formula.

FYI: Line
146| 139 7/21/06 5.92

A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

Any suggestions?

Thanks,

Tom

"Duke Carey" wrote:

Select the cell
Press the F5 key
Click on Special
Click on 'Dependents'
Click on OK

"Tom" wrote:

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom

LOOKUP is delivering inaccurate data

You're correct. BG is not sorted. B & BG is input daily and need to be in
the order they are entered so to activate other formulas in other cells.

However, here is an interesting discovery: I have random numbers in
BG111:BG172 ranging from .02 - 100. When I go to BG 173 and enter a number
less than 24 I get 7/17/06, my the result of my LOOKUP; if between 25 - 40 I
get 7/21/06; if over 41 I get the proper (current) date, which in this case
would be 08/29/06.

This leads me to believe there is a programming glitch related to the LOOKUP
function. Almost to look like I am asking IF <24,7/17/06, IF24<40, 7/21/06,
IF =41,Today().

And all of this started happening after I moved part of the worksheet to
another page within the workbook.

Any further suggestions?

Tom

"Duke Carey" wrote:

Unless your example data is wrong, column BG really IS NOT SORTED

FYI: Line
146| 139 7/21/06 5.92

A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

"Tom" wrote:

BG is chronological data inputed based off of B...the current date.

The formula worked fine before I cut 1/2 of the data and pasted it to
another worksheet within the workbook.

I'm stumped.

Tom

"Duke Carey" wrote:

From the Help file LOOKUP()
---------------------------------------------------------------
Lookup_vector is a range that contains only one row or one column. The
values in lookup_vector can be text, numbers, or logical values.

Important The values in lookup_vector must be placed in ascending order:
...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value. Uppercase and lowercase text are equivalent.
---------------------------------------------------------------

So..you gotta sort BG in ascending order

"Tom" wrote:

That worked fine, but I still can't solve the underlying problem.

The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Line 270, Column B should display what is in B171, but it isn't. Instead it
is going up the sheet and displaying information that isn't asked for in the
formula.

FYI: Line
146| 139 7/21/06 5.92

A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

Any suggestions?

Thanks,

Tom

"Duke Carey" wrote:

Select the cell
Press the F5 key
Click on Special
Click on 'Dependents'
Click on OK

"Tom" wrote:

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom