Populating a field conditional of the input of two cells

[R3df1sh]

Hello All,
I'm trying to write a simple formula that puts a specific answe
conditional of the answers supplied in two other cells. I have followe
the formula structure as I understand it, however microsoft Excel X
doesn't like the (and statements. Could anyone enlighten me on th
correct way of doing this?
Many thanks. (The formula is below)

 

[Don Guillett]

Would this work?
=LOOKUP(F6,{"N","PE","PVC","XLPE"},{2,4,6,8})+IF(F4=1,1,0)

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello All,
I'm trying to write a simple formula that puts a specific answer
conditional of the answers supplied in two other cells. I have followed
the formula structure as I understand it, however microsoft Excel XP
doesn't like the (and statements. Could anyone enlighten me on the
correct way of doing this?
Many thanks. (The formula is below)

=IF(AND(F4=1,F6="XLPE"),"1",IF(AND(F4<>1,F6="XLPE"),"2",IF(AND(F4=1,F6="PE")

 

[R3df1sh]

Don,
Thanks for your speedy response. I am attempting to read up on the
lookup value to see if I can use that route but with the formula you
provided it does not provide the necessary output. It adds the value of
F4 to the total. To try to better explain what I am attempting to do
I'll break down the values.

If F4=1 & F6 ="XLPE" Value to field=1
If F4<>1 & F6 ="XLPE" Value to field=2
If F4=1 & F6 ="PE" Value to field=3
If F4<>1 & F6 ="PE" Value to field=4
If F4=1 & F6 ="PVC" Value to field=5
If F4<>1 & F6 ="PVC" Value to field=6
If F4=1 & F6 ="N" Value to field=7
If F4<>1 & F6 ="N" Value to field=8
If F4 (Value Does Not Meet Criteria) & F6 (Value Does Not Meet
Criteria) Value to field=Error

Formula To Be Used in an Excel XP worksheet.

 

[Don Guillett]

try
=IF(ISNA(LOOKUP(F6,{"N","PE","PVC","XLPE"},{2,4,6,8})+IF(F4=1,1,0)),"",LOOKU
P(F6,{"N","PE","PVC","XLPE"},{2,4,6,8})+IF(F4=1,1,0))

 

[Chris Shipley]

I would build a lookup table (I've put it in Sheet2):
XLPE 1
PE 3
PVC 5
N 6

Then use this:
=IF(NOT(ISERROR(MATCH(F6,Sheet2!$A1:$A$4,0))),IF(F4=1,0,1)+VLOOKUP(F6,Sheet2
!$A1:$B$4,2,0),"Error")

HTH