|
When IF has limited iterations
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=F1 0,E4,"-")))))))) There has to be a better way. Help please. Thank you. Drew. |
When IF has limited iterations
Have a look in the help index for
OR -- Don Guillett Microsoft MVP Excel SalesAid Software "Drew" wrote in message ... 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=F1 0,E4,"-")))))))) There has to be a better way. Help please. Thank you. Drew. |
When IF has limited iterations
Try this:
=IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4) Hope this helps. Pete On Jun 27, 1:01*pm, "Drew" wrote: 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=F 10,E4,"-")))))))) There has to be a better way. Help please. Thank you. Drew. |
When IF has limited iterations
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,L 4=F10,M4=F10),E4,"-") Regards, Stefi €˛Drew€¯ ezt Ć*rta: 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=F1 0,E4,"-")))))))) There has to be a better way. Help please. Thank you. Drew. |
When IF has limited iterations
Yes, much better!
Stefi €˛Pete_UK€¯ ezt Ć*rta: Try this: =IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4) Hope this helps. Pete On Jun 27, 1:01 pm, "Drew" wrote: 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. |
When IF has limited iterations
Thanks, Stefi - much appreciated.
Pete On Jun 27, 1:31*pm, Stefi wrote: Yes, much better! Stefi „Pete_UK” ezt ķrta: |
When IF has limited iterations
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. "Pete_UK" wrote in message ... Try this: =IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4) Hope this helps. Pete On Jun 27, 1:01 pm, "Drew" wrote: 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=F 10,E4,"-")))))))) There has to be a better way. Help please. Thank you. Drew. |
When IF has limited iterations
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. "Pete_UK" wrote in message ... Try this: =IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4) Hope this helps. Pete On Jun 27, 1:01 pm, "Drew" wrote: 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=F 10,E4,"-")))))))) There has to be a better way. Help please. Thank you. Drew. |
When IF has limited iterations
You could try this amendment:
=IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4*COUNTIF(F4:Q4,F10)) Hope this helps. Pete On Jun 29, 2:22*pm, "Drew" wrote: 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. |
When IF has limited iterations
perfect ... thank you Pete.
"Pete_UK" wrote in message ... You could try this amendment: =IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4*COUNTIF(F4:Q4,F10)) Hope this helps. Pete On Jun 29, 2:22 pm, "Drew" wrote: 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. |
When IF has limited iterations
You're welcome, Drew - thanks for feeding back.
Pete On Jun 30, 12:12*am, "Drew" wrote: perfect ... thank you Pete. "Pete_UK" wrote in message ... You could try this amendment: =IF(ISNA(MATCH(F10,F4:Q4,0)),"-",E4*COUNTIF(F4:Q4,F10)) Hope this helps. Pete On Jun 29, 2:22 pm, "Drew" wrote: 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.- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 11:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com