# IF Function

Celia Muller 4 days ago

Be a bit more Specific, what word and where is that word (criteria) from aother CELL Value or a Fixed word in Function "Word" ? . =IF(L3<>"Word","",H86) or =IF(L3="Word",H86,"") .

• "Word" can be Replaced by a CELL.REF.
1 Like

Lacey Jenkins 5 days ago

Lucy Kemmer you can obviously accomplish this in many ways, here are few alternatives which i would like to share with you

• Formula used in cell A2

=LOOKUP(MOD(F2,1)*24,{0,5,16,22},{"Night","Morning","Evening","Night"})

This is without any table of reference and is hard coded.

• Formula used in cell B2

= IF(HOUR(F2)>=22,"Night",IF(HOUR(F2)>=16,"Evening",IF(HOUR(F2)>=5,"Morning","Night")))

• Formula used in cell C2

=VLOOKUP(MOD(F2,1),\$H\$2:\$I\$5,2,1)

This uses the same table reference like the below two more

• Formula used in cell D2

=XLOOKUP(MOD(F2,1),\$H\$2:\$H\$5,\$I\$2:\$I\$5,,-1)

• Formula used in cell E2

=LOOKUP(MOD(F2,1),\$H\$2:\$I\$5)

0 Likes

Candida Greenholt II 5 days ago

Ctrl+F and write the word that you are searching

2 Likes

Rachelle Pfeffer II 1 week ago

it should start with =if( F2, "", DATEDIF(F2,B2,"d")) , so it does nothing if F is empty, but does whats asked if F2 is whatever.

3 Likes

Devante Kshlerin 1 week ago

It's not yet solved.

I'm getting the date stamp successfully but...

''The issue is that it resets every time Column ''M'' updates because of the external query that i'm running. Any way around that?''

0 Likes

Lacey Jenkins 1 week ago

Jose Daugherty you can use either of the formulas to accomplish your desired output. Excel dependent note that.

• Formula used in cell J3

=IFERROR(INDEX(\$G\$3:\$G\$21,AGGREGATE(15,6,(ROW(\$G\$3:\$G\$21)-ROW(\$G\$3)+1)/(\$I3=\$F\$3:\$F\$21),COUNTIF(I\$3:I3,I3))),"")

Fill Down and applicable to Excel 2010+ onwards.

• Formula used in cell K3

=IFERROR(INDEX(\$G\$3:\$G\$21,SMALL(IF(\$I3=\$F\$3:\$F\$21,ROW(\$G\$3:\$G\$21)-ROW(\$G\$3)+1,""),COUNTIF(I\$3:I3,I3))),"")

Fill Down & Formula applicable to Excel 2007+ onwards.

• Formula used in cell L3

=IFERROR(INDEX(FILTER(\$G\$3:\$G\$21,I3=\$F\$3:\$F\$21,""),COUNTIF(I\$3:I3,I3)),"")

Fill Down & Formula applicable to Excel 2019+ onwards.

Note for Excel 2019 you need to have valid MS Office 2019 License.

1 Like

Alvena Schinner 1 week ago

try: =IF(OR(AND(O34<15000,O36="Yes"),AND(O35="Yes",O36="Yes")),0,O34)

The formula uses the OR function to check two different conditions:

1. If O34 < 15,000 and O36 = "Yes".

2. If both O35 and O36 = "Yes".

If either of these conditions are true, the formula returns 0. If neither of these conditions are true, the formula returns the value of O34. Hope it helps

0 Likes

Rudy Lubowitz 1 week ago

You can only do this with VBA if you want it to automatically enter each time you add a row. Personally, I'd just input the date one time into a large amount of rows, then delete off the ones you don't use at the end of the day.

2 Likes

Destin Boyle 2 weeks ago

Try this, I think this is what you want?

=INDEX('Components'!A9:A1000,MATCH(C4,'Components'!A4:A1000,0),MATCH(INDEX('CBOM'!E2:U2,MATCH(C4,'Components'!A4:A1000,0)),'CBOM'!E1:U1,0))

0 Likes