| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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: |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| iterations? | Excel Worksheet Functions | |||
| How many iterations it takes to get to 0? | Excel Worksheet Functions | |||
| # of iterations | Excel Worksheet Functions | |||
| Iterations | New Users to Excel | |||
| Is there a way where I can default iterations | Excel Discussion (Misc queries) | |||
![Drew[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
