Lookup function help please

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
 
L

Luke M

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,))))
 
T

T. Valko

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)
 
F

Fred Smith

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
 
C

Colette

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

Colette
 
C

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...
 
C

Colette

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...
 
C

Colette

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...
 
C

Colette

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function or Sub to help with text string 5
Index and Match 8
LOOKUP HELP PLEASE 1
help needed with lookup and related functions 3
Lookup functions always return n/a ??? 3
Lookup Problem with Index and Match 3
LOOKUP returning wrong values 2
Include calculations in the lookup function 8

Top