When IF has limited iterations

[Drew]

Hello everyone.

I am wanting to use the following IF formula and it only covers 8 out of 12
cells in a row that I need to extract data from.

=IF(F4=F10,E4,IF(G4=F10,E4,IF(H4=F10,E4,IF(I4=F10,E4,IF(J4=F10,E4,IF(K4=F10,E4,IF(L4=F10,E4,IF(M4=F10,E4,"-"))))))))

There has to be a better way. Help please.

Thank you. Drew.

 

[Don Guillett]

Have a look in the help index for
OR

 

[Pete_UK]

Try this:

=IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4)

Hope this helps.

Pete

 

[Stefi]

Don meant this way (I've composed the formula before having read Don's post):
=IF(OR(F4=F10,G4=F10,H4=F10,I4=F10,J4=F10,K4=F10,L4=F10,M4=F10),E4,"-")
Regards,
Stefi


„Drew†ezt írta:

 

[Stefi]

Yes, much better!
Stefi


„Pete_UK†ezt írta:

 

[Pete_UK]

Thanks, Stefi - much appreciated.

Pete

 

[Drew]

You guys 'n' girls are awesome. Thanks Pete.

But why didnt this version work?

=IF($F$4:$Q$4=F10,$E$4,"-")

Help much appreciated.

Goodnight from Perth, Australia.

Try this:

=IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4)

Hope this helps.

Pete

 

[Drew]

Pete et al ... now I have identified a problem. Not yours but mine.

In the F4:Q4 range, if the match ( to F10) is found twice, the formula only
returns one instance of E4.

Is there a minor modification that would multipy E4 by the number of matches
that are found F4:Q4 please?

Kindest thanks for the assistance.

Drew.

Try this:

=IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4)

Hope this helps.

Pete

 

[Pete_UK]

You could try this amendment:

=IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4*COUNTIF(F4:Q4,F10))

Hope this helps.

Pete

 

[Drew]

perfect ... thank you Pete.

You could try this amendment:

=IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4*COUNTIF(F4:Q4,F10))

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Drew - thanks for feeding back.

Pete