Lookup function returns #NUM! error

  • Thread starter Thread starter Antje Crawford
  • Start date Start date
A

Antje Crawford

Hello,
I have the function below, which returns the corresponding
label to a value that is extracted from a table on a
different sheet in the same workbook.
The formula goes as follows (I shortened the argument list
for the MAX formula for better readability):

=LOOKUP(MAX(Yields!D11,Yields!D14,Yields!D30),Yields!
D:D,LEFT(Yields!A:A,FIND(" ",Yields!A:A)-1))

I replaced the result_vector of the lookup formula with
a 'LEFT' formula (with regards to John Walkenbach's Excel
2000 Formulas book).Nonetheless, in doing so the formula
returns the #NUM! error. If I take the LEFT formula out
and replace it with a simple result_vector, such as Yields!
A:A, the lookup formula is working correctly.
Can anybody explain why this is happening, and maybe has a
different solution or recommendation?
All help is greatly appreciated.
TIA.
BR, Antje Crawford
 
Antje Crawford said:
Hello,
I have the function below, which returns the corresponding
label to a value that is extracted from a table on a
different sheet in the same workbook.
The formula goes as follows (I shortened the argument list
for the MAX formula for better readability):

=LOOKUP(MAX(Yields!D11,Yields!D14,Yields!D30),Yields!
D:D,LEFT(Yields!A:A,FIND(" ",Yields!A:A)-1))

I replaced the result_vector of the lookup formula with
a 'LEFT' formula (with regards to John Walkenbach's Excel
2000 Formulas book).Nonetheless, in doing so the formula
returns the #NUM! error. If I take the LEFT formula out
and replace it with a simple result_vector, such as Yields!
A:A, the lookup formula is working correctly.
Can anybody explain why this is happening, and maybe has a
different solution or recommendation?
All help is greatly appreciated.
TIA.
BR, Antje Crawford
Click to expand...

I have not read the book you mention so cannot comment on whether or not you
are following what it describes. However, I think you must have a range,
rather than a function, in the result_vector. To achieve what you want, I
think you must put the LEFT function around the LOOKUP, like this:

=LEFT(LOOKUP(MAX(Yields!D11,Yields!D14,Yields!D30),Yields!D:D,Yields!A:A),FI
ND("
",LOOKUP(MAX(Yields!D11,Yields!D14,Yields!D30),Yields!D:D,Yields!A:A))-1)
 
Back
Top