Formula is returning #VALUE! in excel cell

[Jerry Manner]

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

 

[Roger Govier]

Hi Jerry

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

 

[Mike H]

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]

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










- Tekst uit oorspronkelijk bericht weergeven -

Hi Mike

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

Kind Regards

 

[Roger Govier]

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