Populating a field conditional of the input of two cells

  • Thread starter Thread starter R3df1sh
  • Start date Start date
R

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)
 
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)
R3df1sh said:
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)
Click to expand...
=IF(AND(F4=1,F6="XLPE"),"1",IF(AND(F4<>1,F6="XLPE"),"2",IF(AND(F4=1,F6="PE")
 
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.
 
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))
 
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
 
Back
Top