IF FUNCTION FIRST, THEN WITH INDEX AND MATCH

[IDRIS86]

Hi,

Below formula works well,

=(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O28='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P28='PRICE LIST'!$B$7368:$B$7679),0)))

But I tried adding IF condition at first, but it doesn't work. Highlighted the references added. Please assist.

IF(P27='PRICE LIST'!$B$7368:$B$7679,(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O27='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P27='PRICE LIST'!$B$7368:$B$7679),0))),List!$J$21)

 

[IDRIS86]

Hi I tried this formula, it works well.

=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF($S27="NO",List!$BN$5)))

 

[etaf]

OK,
The formula is under data validation - in the LIST Option
=IF(AND(I27="yes",J27="GALV.STEEL"),B_7800,IF(AND(I27="yes",OR(J27="SS304",J27="SS316")),D_7800,""))
AND you need to define named ranges , as i mentioned before
And named the ranges
Define Named Ranges
B7800 to B7814 = B_7800 I used that NAME for simplicity - BUT used a different range for simplicity
D7800 to D7808 = D_7800 I used that NAME for simplicity - BUT used a different range for simplicity

In the CELL K27 =IF(OR(I27="NO",I27="NA"),NA,IF(AND(I27="yes",J27="GALV.STEEL"),"B_7800",IF(AND(I27="yes",OR(J27="SS304",J27="SS316")),"D_7800","")))
Where the "B_7800" is the first entry in your LIST and you could replace for the actual 1st entry in your list 'price list'!B7800
AND again the "D_7800" is the first entry in your LIST and you could replace for the actual 1st entry in your list 'price list'!D7800
I have updated the formula to show the cells where I have the LIST B3 and D3 - just to make it simple for me to re-create and not use a row 7800's
to show that change pointing to the first cell in the list to get a default value
=IF(OR(I27="NO",I27="NA"),NA,IF(AND(I27="yes",J27="GALV.STEEL"),'price list'!B7800,IF(AND(I27="yes",OR(J27="SS304",J27="SS316")),'price list'!D7800,"")))

I may not be very clear as there is a lot going on here
Named ranges
Formula in LIST data validation
Formula in Cell itself

here is the sample again

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Now it maybe if you put your spreadsheet on a share, I may be able to help with the real data - ASSUMING you can put on a PUBLIC forum

 

[etaf]

glad you managed to get it working , I was not sure how clear i was making it
any all sorted now

Seems to have covered all you wanted with this
=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF($S27="NO",List!$BN$5)))

Although the cells are a different reference and only covers a "NO" entry NOT a "NA" as originally mentioned
anyway as long as its working for you

My copies on dropbox will only be on for a few days

 

[IDRIS86]

Yes I named a table for Galvanized & SS separately. If I simply select cell ranges it doesn't work. As you advised I created a table name for my convenience. It works . Thanks a lot for your support. Yes you are correct. I didn't include NA in my formula, only it was included NO. I believe need to use AND function in the last to have NO & NA .

is this correct

=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(AND($S27="NO",$S27="NA",List!$BN$5))))

Also is there any other way that I can contact you here in this forum for any other clarifications.

 

[etaf]

Also is there any other way that I can contact you here in this forum for any other clarifications.

You can message me . but to be honest there are very clever members here who are able to answer questions on excel - so i'm sure they will be able to answer

NO its NOT an AND , as AND needs all conditions to be true - and S27 cannot be NO and NA at the same time - you need an OR()

=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(AND($S27="NO",$S27="NA",List!$BN$5))))

change
=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(AND($S27="NO",$S27="NA",List!$BN$5))))
to
=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(OR($S27="NO",$S27="NA"),List!$BN$5))))
You also do not have a statement for if ALL are FALSE - at the moment that would be FALSE , so for completeness and if used in a cell rather than a LIST
=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(OR($S27="NO",$S27="NA"),List!$BN$5,""))))
Now the cell will remain blank rather than display FALSE

 

[IDRIS86]

May I know the difference between these formulas,

=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(OR($S27="NO",$S27="NA"),List!$BN$5))))
You also do not have a statement for if ALL are FALSE - at the moment that would be false , so for completeness
=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(OR($S27="NO",$S27="NA"),List!$BN$5,""))))

Could you advise what is the purpose of the last double quotation mark as highlighted in the second formula.

I tried applied this formula. It shows error. I dont know why.

 

[etaf]

to many )))

=IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(OR($S27="NO",$S27="NA"),List!$BN$5,"")))
should work

This completes the final nested IF
IF(OR($S27="NO",$S27="NA"),List!$BN$5,"")
should all follow the
IF(TEST,TRUE,FALSE) format
if you do
IF(TEST,TRUE)
Not a problem - just if the TEST is false then the cell will display FALSE
which you may not want
so a ""
will just leave the cell blank

 

[IDRIS86]

First line item comes correct. When I checked SS304 in second line item, it doesn't show the dropdown. I made an error. I just corrected to $T27 for SS304.
Thank you much for your time & patience in assisting me with all queries.

IF(AND($S27="YES",$T27="GALV.STEEL"),GISLEEVETYPE,IF(AND($S27="YES",OR(T$27="SS304",$T27="SS316")),SSSLEEVETYPE,IF(OR($S27="NO",$S27="NA"),List!$BN$5,"")))

 

[etaf]

pleasure to help
glad its all working for you now

 

[IDRIS86]

Hi,

I write this formula for other conditions, but I'm unable to copy this in DATA validation. I tried to paste. It doesn't copy into the SOURCE. Would you help.

'=IF($N29="FSD",ACTUATORTYPE,IF($N29="SD",ACTUATORTYPE,IF(AND($N29="CD",OR($V29="NO",$V29="HAND QUAD")),List!$C$23,IF(AND($N29="CD",$V29="YES"),ACTUATORTYPE,IF($N29="FD",List!$H$21,IF($N29="LOUVER",List!$H$21,IF($N29="ATTENUATOR",List!$H$21,IF($N29="NRD",List!$H$21,IF($N29="BLAST.DAMPER",List!$H$21)))))))))