Lookup function help please

  • Thread starter Thread starter Colette
  • Start date Start date
C

Colette

I'm trying to use the following LookUp function but it keeps on coming up as
#N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where
I'm going wrong

=LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"})

The AU17 cell has the following formula in it, and it works - this is for VAT
=IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,))))

Many Thanks for your help
 
Remove the quotations marks around the 1 in AU17's formula:
=IF(VC1="U",1, IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,))))
 
Remove all the quotes from around any numbers.
=IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,))))
=IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3,IF(VC1="P",4))))

=LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"})
Click to expand...

=LOOKUP(AU17,{1,2,3,4},{0.175,0,0,0.05})

Alternative to LOOKUP:

=CHOOSE(AU17,0.175,0,0,0.05)
 
Your problem is the quote marks around the "1". Quotes make the cell text.
Without quotes it's a number, and you need a number in your Lookup
statement. So change AU17 to:
=IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,""))))
I also changed your last IF statement to return nothing ("") if the
condition is not met. Otherwise you would get FALSE.
The fact that there are spaces in the IF statement shown tells us you
manually typed the formula into your e-mail. This is fraught with error.
Always cut and paste your formula, to avoid typing errors.

Finally, it's very likely your Lookup formula should be:
=LOOKUP(AU17,{1,2,3,4},{.175,0,0,0.05})

Regards,
Fred
 
Thanks again for all suggestions...I'd been tearing my hair out for something
that was 'staring me in the face'

Colette
 
Thanks Daryl S

Colette

Daryl S said:
Colette -

You had 1 being entered as text into AU17. Try this:

=IF(VC1="U",1, IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,))))
Click to expand...
 
Many Thanx Fred

Colette

Fred Smith said:
Your problem is the quote marks around the "1". Quotes make the cell text.
Without quotes it's a number, and you need a number in your Lookup
statement. So change AU17 to:
=IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,""))))
I also changed your last IF statement to return nothing ("") if the
condition is not met. Otherwise you would get FALSE.
The fact that there are spaces in the IF statement shown tells us you
manually typed the formula into your e-mail. This is fraught with error.
Always cut and paste your formula, to avoid typing errors.

Finally, it's very likely your Lookup formula should be:
=LOOKUP(AU17,{1,2,3,4},{.175,0,0,0.05})

Regards,
Fred



.
Click to expand...
 
Many Thanks Luke

Colette

Luke M said:
Remove the quotations marks around the 1 in AU17's formula:
=IF(VC1="U",1, IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,))))


--
Best Regards,

Luke M



.
Click to expand...
 
Many Thanks T. Valko

Colette

T. Valko said:
Remove all the quotes from around any numbers.


=LOOKUP(AU17,{1,2,3,4},{0.175,0,0,0.05})

Alternative to LOOKUP:

=CHOOSE(AU17,0.175,0,0,0.05)

--
Biff
Microsoft Excel MVP





.
Click to expand...
 
Back
Top