Hi

I have 3 worksheets. The first is called Sheet1 and the third
LookupData. On the third sheet I have 2 columns ( A and B) in which
have a list of numbers ( 8 rows). On the first sheet I have a cell
with the following formula:
=LOOKUP(LEFT(C86;3);"LookupData!$A$1:LookupData!$A $8";"LookupData!$B
$1:LookupData!$B$8")

The formula checks the data from another cell ( C86) and take the 3
leftmost characters and matches that with a cell in column A on the
third sheet. But which data is selected in C86, the cell with the
formula allways shows the error data : #VALUE!

What can be wrong? The 2 columns on the third tab are both sorted
ascendiing.

Any help will be appreciated.

Regards

Hi Jerry

Try
=LOOKUP(LEFT(C86;3);"LookupData!$A$1$B$8)


--
Regards
Roger Govier

"Jerry Manner" wrote in message
...
Hi

I have 3 worksheets. The first is called Sheet1 and the third
LookupData. On the third sheet I have 2 columns ( A and B) in which
have a list of numbers ( 8 rows). On the first sheet I have a cell
with the following formula:
=LOOKUP(LEFT(C86;3);"LookupData!$A$1:LookupData!$A $8";"LookupData!$B
$1:LookupData!$B$8")

The formula checks the data from another cell ( C86) and take the 3
leftmost characters and matches that with a cell in column A on the
third sheet. But which data is selected in C86, the cell with the
formula allways shows the error data : #VALUE!

What can be wrong? The 2 columns on the third tab are both sorted
ascendiing.

Any help will be appreciated.

Regards

Jerry,

Its was a syntax problem. Bote that my version of Excel uses , compared tou
your ; so you will need to change back.

=LOOKUP(LEFT(C86,3),lookupdata!A1:A8,lookupdata!B1 :B8)

Mike

"Jerry Manner" wrote:

Hi

I have 3 worksheets. The first is called Sheet1 and the third
LookupData. On the third sheet I have 2 columns ( A and B) in which
have a list of numbers ( 8 rows). On the first sheet I have a cell
with the following formula:
=LOOKUP(LEFT(C86;3);"LookupData!$A$1:LookupData!$A $8";"LookupData!$B
$1:LookupData!$B$8")

The formula checks the data from another cell ( C86) and take the 3
leftmost characters and matches that with a cell in column A on the
third sheet. But which data is selected in C86, the cell with the
formula allways shows the error data : #VALUE!

What can be wrong? The 2 columns on the third tab are both sorted
ascendiing.

Any help will be appreciated.

Regards

On 7 apr, 10:51, Mike H wrote:
Jerry,

Its was a syntax problem. Bote that my version of Excel uses , compared tou
your ; so you will need to change back.

=LOOKUP(LEFT(C86,3),lookupdata!A1:A8,lookupdata!B1 :B8)

Mike



"Jerry Manner" wrote:
Hi

I have 3 worksheets. The first is called Sheet1 and the third
LookupData. On the third sheet I have 2 columns ( A and B) in which
have a list of numbers ( 8 rows). On the first sheet I have a cell
with the following formula:
=LOOKUP(LEFT(C86;3);"LookupData!$A$1:LookupData!$A $8";"LookupData!$B
$1:LookupData!$B$8")

The formula checks the data from another cell ( C86) and take the 3
leftmost characters and matches that with a cell in column A on the
third sheet. But which data is selected in C86, the cell with the
formula allways shows the error data : #VALUE!

What can be wrong? The 2 columns on the third tab are both sorted
ascendiing.

Any help will be appreciated.

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Mike

Thank you very much for the correction. It is working now.

Kind Regards

Hi Mike/ Jerry

FYI, you do not need to give the range as two items
lookupdata!A1:A8,lookupdata!B1:B8

is exactly the same as using the shorter

lookupdata!A1:B8

--
Regards
Roger Govier

"Mike H" wrote in message
...
Jerry,

Its was a syntax problem. Bote that my version of Excel uses , compared
tou
your ; so you will need to change back.

=LOOKUP(LEFT(C86,3),lookupdata!A1:A8,lookupdata!B1 :B8)

Mike

"Jerry Manner" wrote:

Hi

I have 3 worksheets. The first is called Sheet1 and the third
LookupData. On the third sheet I have 2 columns ( A and B) in which
have a list of numbers ( 8 rows). On the first sheet I have a cell
with the following formula:
=LOOKUP(LEFT(C86;3);"LookupData!$A$1:LookupData!$A $8";"LookupData!$B
$1:LookupData!$B$8")

The formula checks the data from another cell ( C86) and take the 3
leftmost characters and matches that with a cell in column A on the
third sheet. But which data is selected in C86, the cell with the
formula allways shows the error data : #VALUE!

What can be wrong? The 2 columns on the third tab are both sorted
ascendiing.

Any help will be appreciated.

Regards