Hi,

I'm trying to make up an IF statement but not having much luck, my table reads

Date Area Tank
1/1/06 A Hp
1/1/06 B Lp
2/1/06 A Lp
2/1/06 B Shut
2/1/06 C LP
2/1/06 G Test

I want to have in the formula cell a statement that returns four different
numbers for the four possible text.

i.e: If the data cell in tank reads "Hp" then the formula cell reads "1" or
if the data cell reads "Lp" the formula cell reads "2" and so forth for all
four options so that "Test"=3 and "Shut"=4

Is this possible?

Many thanks,
Ross

Assuming Data Are and Tank are columns A B and C isnt this just a simple
case of in column D entering the formula

=IF(B1="Hp",1,IF(B1="Lp",2,IF(B1=Shut",3,IF(B1="Te st",4,"UNKNOWN"))))

Then copy the formula down the D column

(Im sure theres a simpler formula to use to produce this)


--
Special-K
------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=561109

if you create a table say in e2:f5
Hp 1
Lp 2
Test 3
Shut 4

then in the say cell d2 =vlookup(c2,$e$2:$f$5,2,false) and copy it
down

or

=if(c2="Hp",1,if(c2="Lp",2,if(c2="Test",3,if(c2="s hut",""))))

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=561109

Hi Ross

One way
=LOOKUP(C2,{"hp",1;"lp",2;"shut",4;"test",3})

If you do want to use IF's, then
=IF(C2="hp",1,IF(C2="lp",2,IF(C3="test",3,IF(C2="s hut",4,""))))

--
Regards

Roger Govier


"Ross" wrote in message
...
Hi,

I'm trying to make up an IF statement but not having much luck, my
table reads

Date Area Tank
1/1/06 A Hp
1/1/06 B Lp
2/1/06 A Lp
2/1/06 B Shut
2/1/06 C LP
2/1/06 G Test

I want to have in the formula cell a statement that returns four
different
numbers for the four possible text.

i.e: If the data cell in tank reads "Hp" then the formula cell reads
"1" or
if the data cell reads "Lp" the formula cell reads "2" and so forth
for all
four options so that "Test"=3 and "Shut"=4

Is this possible?

Many thanks,
Ross

That seems the right way, also could use a vlookup function and another table

"Special-K" wrote:


Assuming Data Are and Tank are columns A B and C isnt this just a simple
case of in column D entering the formula

=IF(B1="Hp",1,IF(B1="Lp",2,IF(B1=Shut",3,IF(B1="Te st",4,"UNKNOWN"))))

Then copy the formula down the D column

(Im sure theres a simpler formula to use to produce this)


--
Special-K
------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=561109

=IF(ISNA(LOOKUP(A1,{"Hp","Lp","Test","Shut"},{1,2, 3,4})),"",LOOKUP(A1,{"hp","lp","Test","Shut"},{1,2 ,3,4}))

HTH

"Ross" wrote:

Hi,

I'm trying to make up an IF statement but not having much luck, my table reads

Date Area Tank
1/1/06 A Hp
1/1/06 B Lp
2/1/06 A Lp
2/1/06 B Shut
2/1/06 C LP
2/1/06 G Test

I want to have in the formula cell a statement that returns four different
numbers for the four possible text.

i.e: If the data cell in tank reads "Hp" then the formula cell reads "1" or
if the data cell reads "Lp" the formula cell reads "2" and so forth for all
four options so that "Test"=3 and "Shut"=4

Is this possible?

Many thanks,
Ross

I obviously misunderstood LOOKUP ( my poor excuse is I don't use it!) so
Roger's is the correct way. Sorry!

"Toppers" wrote:


=IF(ISNA(LOOKUP(A1,{"Hp","Lp","Test","Shut"},{1,2, 3,4})),"",LOOKUP(A1,{"hp","lp","Test","Shut"},{1,2 ,3,4}))

HTH

"Ross" wrote:

Hi,

I'm trying to make up an IF statement but not having much luck, my table reads

Date Area Tank
1/1/06 A Hp
1/1/06 B Lp
2/1/06 A Lp
2/1/06 B Shut
2/1/06 C LP
2/1/06 G Test

I want to have in the formula cell a statement that returns four different
numbers for the four possible text.

i.e: If the data cell in tank reads "Hp" then the formula cell reads "1" or
if the data cell reads "Lp" the formula cell reads "2" and so forth for all
four options so that "Test"=3 and "Shut"=4

Is this possible?

Many thanks,
Ross