Conditional Excel formula

nostradamus

nostradamus

Board Regular
Joined
Aug 9, 2010
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I have a complicated Conditional Excel formula that needs improvement on:
Sheet1 =has the MATCH INDEX information
Sheet2 =is where I need the formula to be prepared/edited.

Currently,in Shee2:E2, I have a formula that matches info from Sheet2:C2 to Sheet1:C and pulls the appropriate info from Sheet1:D. So, the current formula in Sheet2:E2works great
{=IF(ISERROR(INDEX(Sheet1!$A:$D,MATCH($C2,Sheet1!$C:$C,FALSE),4)),"",INDEX(Sheet1!$A:$D,MATCH
($C2,Sheet1!$C:$C,FALSE),4))}
However,I have an addition in Sheet2 where info in Col.D needs to be used in determining what goes in Col.E (for instance, if nothing is in Sheet2:D2, then just put whatever info goes there (Col.E2). But if Sheet2:D2 has A or L, then that information needs to be taken into consideration from Sheet1:Col.D. Now,Col.D2-D6 currently has no A or L info; but in Col.D7, if L, then it's P, and if A, then it's CG.

Hopefully,you are able to understand what I am trying to get from this formula. Sheet2:Col.F shows the appropriate results I want.
Please see Sheet1 and Sheet2 info below: Thanks

Sheet1
ABCD

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Order[/TD]
[TD="align: center"]Family[/TD]
[TD="align: center"]Genus[/TD]
[TD="align: center"]FunctionalGroup[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Lepidoptera[/TD]
[TD="align: center"]Pyralidae[/TD]
[TD="align: center"]Petrophila[/TD]
[TD="align: center"]SCR[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Elmidae[/TD]
[TD="align: center"]Ancyronyx[/TD]
[TD="align: center"]SCR/CG[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Gyrinidae[/TD]
[TD="align: center"]Dineutus[/TD]
[TD="align: center"]P[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Helochares[/TD]
[TD="align: center"]CG[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Scirtidae[/TD]
[TD="align: center"]Scirtidae[/TD]
[TD="align: center"]SCR/CG/SHR[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]L=P;A=CG[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Berosus[/TD]
[TD="align: center"]L=P;A=CG[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Hydrocanthus[/TD]
[TD="align: center"]L=P/CG;A=P[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Haliplidae[/TD]
[TD="align: center"]Haliplidae[/TD]
[TD="align: center"]SHR/P[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]Diptera[/TD]
[TD="align: center"]Chironomidae[/TD]
[TD="align: center"]Endochironomus[/TD]
[TD="align: center"]SHR/CG/FC[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]Diptera[/TD]
[TD="align: center"]Chironomidae[/TD]
[TD="align: center"]Glyptotendipes[/TD]
[TD="align: center"]SHR/FC/CG[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Sheet2
ABCDEF

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Order[/TD]
[TD="align: center"]Family[/TD]
[TD="align: center"]Genus[/TD]
[TD="align: center"]Stage[/TD]
[TD="align: center"]Trophic Guild[/TD]
[TD="bgcolor: #99cc00, align: center"]ResultWanted[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Lepidoptera[/TD]
[TD="align: center"]Pyralidae[/TD]
[TD="align: center"]Petrophila[/TD]

[TD="align: center"]SCR[/TD]
[TD="bgcolor: #99cc00, align: center"]SCR[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Elmidae[/TD]
[TD="align: center"]Ancyronyx[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]SCR/CG[/TD]
[TD="bgcolor: #99cc00, align: center"]SCR/CG[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Gyrinidae[/TD]
[TD="align: center"]Dineutus[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]P[/TD]
[TD="bgcolor: #99cc00, align: center"]P[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Helochares[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]CG[/TD]
[TD="bgcolor: #99cc00, align: center"]CG[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Scirtidae[/TD]
[TD="align: center"]Cyphon[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]SCR/CG/SHR[/TD]
[TD="bgcolor: #99cc00, align: center"]SCR/CG/SHR[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #99cc00, align: center"]P[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #99cc00, align: center"]CG[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Berosus[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #99cc00, align: center"]CG[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Berosus[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #99cc00, align: center"]P[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Hydrocanthus[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L=P/CG;A=P[/TD]
[TD="bgcolor: #99cc00, align: center"]P/CG[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Hydrophilidae[/TD]
[TD="align: center"]Hydrocanthus[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]L=P/CG;A=P[/TD]
[TD="bgcolor: #99cc00, align: center"]P[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]Coleoptera[/TD]
[TD="align: center"]Haliplidae[/TD]
[TD="align: center"]Haliplidae[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]SHR/P[/TD]
[TD="bgcolor: #99cc00, align: center"]SHR/P[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]Diptera[/TD]
[TD="align: center"]Chironomidae[/TD]
[TD="align: center"]Endochironomus[/TD]

[TD="align: center"]SHR/CG/FC[/TD]
[TD="bgcolor: #99cc00, align: center"]SHR/CG/FC[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]Diptera[/TD]
[TD="align: center"]Chironomidae[/TD]
[TD="align: center"]Glyptotendipes[/TD]

[TD="align: center"]SHR/FC/CG[/TD]
[TD="bgcolor: #99cc00, align: center"]SHR/FC/CG[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: center"]Odonata[/TD]
[TD="align: center"]Coenagrionidae[/TD]
[TD="align: center"]Argia[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="bgcolor: #99cc00, align: center"]P[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Sort by date Sort by votes
I found it a bit difficult to get everything you wanted in one formula. Instead I left your E2 formula alone (mostly - I rewrote it using IFERROR which has been available since Excel 2007), and added a new column to extract the part of the response you wanted. Put the formula in F2 and copy down. It also uses IFERROR, so if you have an older version of Excel we'll have to fall back and retry.

ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Order[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Family[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Genus[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Stage[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Trophic Guild[/TD]
[TD="bgcolor: #FAFAFA, align: center"]ResultWanted[/TD]
[TD="bgcolor: #99CC00, align: center"]ResultWanted[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Lepidoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Pyralidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Petrophila[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: center"]SCR[/TD]
[TD="bgcolor: #FAFAFA, align: center"]SCR[/TD]
[TD="bgcolor: #99CC00, align: center"]SCR[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Elmidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Ancyronyx[/TD]
[TD="bgcolor: #FAFAFA, align: center"]A[/TD]
[TD="bgcolor: #FAFAFA, align: center"]SCR/CG[/TD]
[TD="bgcolor: #FAFAFA, align: center"]SCR/CG[/TD]
[TD="bgcolor: #99CC00, align: center"]SCR/CG[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Gyrinidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Dineutus[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L[/TD]
[TD="bgcolor: #FAFAFA, align: center"]P[/TD]
[TD="bgcolor: #FAFAFA, align: center"]P[/TD]
[TD="bgcolor: #99CC00, align: center"]P[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Helochares[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L[/TD]
[TD="bgcolor: #FAFAFA, align: center"]CG[/TD]
[TD="bgcolor: #FAFAFA, align: center"]CG[/TD]
[TD="bgcolor: #99CC00, align: center"]CG[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Scirtidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Cyphon[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L[/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #99CC00, align: center"]SCR/CG/SHR[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #FAFAFA, align: center"]P[/TD]
[TD="bgcolor: #99CC00, align: center"]P[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]A[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #FAFAFA, align: center"]CG[/TD]
[TD="bgcolor: #99CC00, align: center"]CG[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Berosus[/TD]
[TD="bgcolor: #FAFAFA, align: center"]A[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #FAFAFA, align: center"]CG[/TD]
[TD="bgcolor: #99CC00, align: center"]CG[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Berosus[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L=P;A=CG[/TD]
[TD="bgcolor: #FAFAFA, align: center"]P[/TD]
[TD="bgcolor: #99CC00, align: center"]P[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrocanthus[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L=P/CG;A=P[/TD]
[TD="bgcolor: #FAFAFA, align: center"]P/CG[/TD]
[TD="bgcolor: #99CC00, align: center"]P/CG[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrophilidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Hydrocanthus[/TD]
[TD="bgcolor: #FAFAFA, align: center"]A[/TD]
[TD="bgcolor: #FAFAFA, align: center"]L=P/CG;A=P[/TD]
[TD="bgcolor: #FAFAFA, align: center"]P[/TD]
[TD="bgcolor: #99CC00, align: center"]P[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coleoptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Haliplidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Haliplidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]A[/TD]
[TD="bgcolor: #FAFAFA, align: center"]SHR/P[/TD]
[TD="bgcolor: #FAFAFA, align: center"]SHR/P[/TD]
[TD="bgcolor: #99CC00, align: center"]SHR/P[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Diptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Chironomidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Endochironomus[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: center"]SHR/CG/FC[/TD]
[TD="bgcolor: #FAFAFA, align: center"]SHR/CG/FC[/TD]
[TD="bgcolor: #99CC00, align: center"]SHR/CG/FC[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Diptera[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Chironomidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Glyptotendipes[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: center"]SHR/FC/CG[/TD]
[TD="bgcolor: #FAFAFA, align: center"]SHR/FC/CG[/TD]
[TD="bgcolor: #99CC00, align: center"]SHR/FC/CG[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Odonata[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Coenagrionidae[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Argia[/TD]
[TD="bgcolor: #FAFAFA, align: center"]P[/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #99CC00, align: center"]P[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IFERROR(INDEX(Sheet1!$A:$D,MATCH($C2,Sheet1!$C:$C,FALSE),4),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(D2="",E2,IFERROR(MID(E2,SEARCH(D2&"=",E2)+2,FIND(";",E2&";",SEARCH(D2&"=",E2))-SEARCH(D2&"=",E2)-2),E2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I didn't get a match on E6, and the G16 Result Wanted value seems to be a typo.

Now if you want you can hide column E.

Hope this helps.
 
Upvote 0
Eric, your formula works great, thanks a bunch. One more question tho - if there is no match, then I want the cell to remain blank, currently with your formula a no match returns a "0". Could you pl add on to your formula the "" part? :eeek:.
 
Upvote 0
Just add &"" to the end, like this:

=IFERROR(INDEX(Sheet1!$A:$D,MATCH($C2,Sheet1!$C:$C,FALSE),4),"")&""

;)
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top