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)

 

[etaf]

I cant see the issue yet - BUT you should join all the results with the same condition
IE
Group all the List!H21 , LIST!C23 & ACTUATORTYPE

I think this is a shortened version

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

AND changing the cells to name range
=IF(OR(N29="FD",N29="LOUVER",N29="ATTENUATOR",N29="NRD",N29="BLAST.DAMPER"),LIST_H21,IF(OR(N29="FSD",N29="SD",AND(N29="CD",V29="YES")),ACTUATORTYPE,IF(AND(N29="CD",OR(V29="NO",V29="HAND QUAD")),LIST_C23,"")))
Does Not help still not working

Not sure why yet, also may not be around now over easter

 

[etaf]

OK , I got it to work, by making 1 of the cells conditions actually contain info

ALSO as you have version 365 - you can use the IFS() function
Which also works
=IFS(OR(N29="FD",N29="LOUVER",N29="ATTENUATOR",N29="NRD",N29="BLAST.DAMPER"),LIST_H21,OR(N29="FSD",N29="SD",AND(N29="CD",V29="YES")),ACTUATORTYPE,AND(N29="CD",OR(V29="NO",V29="HAND QUAD")),LIST_C23)

And where only 1 cell referenced change back to a direct link

=IFS(OR(N29="FD",N29="LOUVER",N29="ATTENUATOR",N29="NRD",N29="BLAST.DAMPER"),list!H21,OR(N29="FSD",N29="SD",AND(N29="CD",V29="YES")),ACTUATORTYPE,AND(N29="CD",OR(V29="NO",V29="HAND QUAD")),list!C23)

I just populate N29 cell

Dropbox - File Deleted - Simplify your life

www.dropbox.com

DataVal-ETAF-v3.xlsx
	J	K	L	M	N	O
22
23				ACTUATORTYPE	listH21	listc23
24				ACTUATORTYPE	H21	C23
25	Drop Down =	b		b
26				c
27				d
28
29					FSD
30
31
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
K25:K31	List	=IFS(OR(N29="FD",N29="LOUVER",N29="ATTENUATOR",N29="NRD",N29="BLAST.DAMPER"),list!H21,OR(N29="FSD",N29="SD",AND(N29="CD",X29="YES")),ACTUATORTYPE,AND(N29="CD",OR(X29="NO",X29="HAND QUAD")),list!C23)

 

[IDRIS86]

Hi. Thanks. It works. But I rewrite the formula for other column with other conditions, it says too few argument. Sorry I keep bothering you for same subject. I'm practicing to understand this concept, but still facing difficulties.

=IFS(OR($N27="LOUVER",$N27="ATTENUATOR",$N27="NRD",$N27="BLAST.DAMPER"),List!$H$22,OR($N27="FSD",$N27="SD",$N27="FD",AND($N27="CD",$R27="NO")),SLEEVEIF,AND($N27="CD",$R27="YES"), List!$V$6)

 

[etaf]

works OK for me , make sure there is data in ALL the cells specified
listh22
Listv6
SleeveIF named range
and at least one of the criteria cells

I found blank cells give an error

you dont need $'s as its a fixed formula in data validation and not copying at all -

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[IDRIS86]

Thanks. I will correct it.

May I know the difference between these functions OR & AND which you have used for multiple conditions in DATA validation & in normal formula in cell.

Does normal cell formula is applicable for DATA validation as well.?

 

[etaf]

not sure i fully understand the question

OR() will return a TRUE if ANY of the items listed are TRUE
AND() will return a TRUE only if ALL the items listed are TRUE

SO when mixed
AND ( 1 , OR ( 2, 3) )
The result MUST have a 1 in AND must also have either (OR) a 2 or a 3
OR( 1 , AND(2,3)

The result TRUE could be a 1 on its OWN or it could be a 2 AND a 3 - must be true

the dollar$ fixes a cell location, so when you copy/paste move, fill the $ reference does not change A1 moved down and across - will become B2 - But with a $ $A1 , becomes $A2. AND A$1 becomes B$1

In the LIST data validation you are only interested in a TRUE result
Also you are not moving the formula anywhere , so the $ dont do anything