Lookup function help please
 

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

Lookup function help please
 

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
"Colette" wrote in message
...
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

Lookup function help please
 

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"})

=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


"Colette" wrote in message
...
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

Lookup function help please
 

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

"Colette" wrote in message
...
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

Lookup function help please
 

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

--
Daryl S


"Colette" wrote:

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

Lookup function help please
 

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

Colette

"Colette" wrote:

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

Lookup function help please
 

Thanks Daryl S

Colette

"Daryl S" wrote:

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

--
Daryl S


"Colette" wrote:

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

Lookup function help please
 

Many Thanks Luke

Colette

"Luke M" wrote:

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
"Colette" wrote in message
...
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


.

Lookup function help please
 

Many Thanx Fred

Colette

"Fred Smith" wrote:

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

"Colette" wrote in message
...
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

.

Lookup function help please
 

Many Thanks T. Valko

Colette

"T. Valko" wrote:

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"})

=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


"Colette" wrote in message
...
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


.

Lookup function help please
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Colette" wrote in message
...
Many Thanks T. Valko

Colette

"T. Valko" wrote:

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"})

=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


"Colette" wrote in message
...
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


.