Conditional Excel formula

[nostradamus]

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

	A	B	C	D
1	Order	Family	Genus	FunctionalGroup
2	Lepidoptera	Pyralidae	Petrophila	SCR
3	Coleoptera	Elmidae	Ancyronyx	SCR/CG
4	Coleoptera	Gyrinidae	Dineutus	P
5	Coleoptera	Hydrophilidae	Helochares	CG
6	Coleoptera	Scirtidae	Scirtidae	SCR/CG/SHR
7	Coleoptera	Hydrophilidae	Hydrophilidae	L=P;A=CG
8	Coleoptera	Hydrophilidae	Berosus	L=P;A=CG
9	Coleoptera	Hydrophilidae	Hydrocanthus	L=P/CG;A=P
10	Coleoptera	Haliplidae	Haliplidae	SHR/P
11	Diptera	Chironomidae	Endochironomus	SHR/CG/FC
12	Diptera	Chironomidae	Glyptotendipes	SHR/FC/CG

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sheet2

	A	B	C	D	E	F
1	Order	Family	Genus	Stage	Trophic Guild	ResultWanted
2	Lepidoptera	Pyralidae	Petrophila		SCR	SCR
3	Coleoptera	Elmidae	Ancyronyx	A	SCR/CG	SCR/CG
4	Coleoptera	Gyrinidae	Dineutus	L	P	P
5	Coleoptera	Hydrophilidae	Helochares	L	CG	CG
6	Coleoptera	Scirtidae	Cyphon	L	SCR/CG/SHR	SCR/CG/SHR
7	Coleoptera	Hydrophilidae	Hydrophilidae	L	L=P;A=CG	P
8	Coleoptera	Hydrophilidae	Hydrophilidae	A	L=P;A=CG	CG
9	Coleoptera	Hydrophilidae	Berosus	A	L=P;A=CG	CG
10	Coleoptera	Hydrophilidae	Berosus	L	L=P;A=CG	P
11	Coleoptera	Hydrophilidae	Hydrocanthus	L	L=P/CG;A=P	P/CG
12	Coleoptera	Hydrophilidae	Hydrocanthus	A	L=P/CG;A=P	P
13	Coleoptera	Haliplidae	Haliplidae	A	SHR/P	SHR/P
14	Diptera	Chironomidae	Endochironomus		SHR/CG/FC	SHR/CG/FC
15	Diptera	Chironomidae	Glyptotendipes		SHR/FC/CG	SHR/FC/CG
16	Odonata	Coenagrionidae	Argia	P	P	P

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Eric W]

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.

	A	B	C	D	E	F	G
1	Order	Family	Genus	Stage	Trophic Guild	ResultWanted	ResultWanted
2	Lepidoptera	Pyralidae	Petrophila		SCR	SCR	SCR
3	Coleoptera	Elmidae	Ancyronyx	A	SCR/CG	SCR/CG	SCR/CG
4	Coleoptera	Gyrinidae	Dineutus	L	P	P	P
5	Coleoptera	Hydrophilidae	Helochares	L	CG	CG	CG
6	Coleoptera	Scirtidae	Cyphon	L			SCR/CG/SHR
7	Coleoptera	Hydrophilidae	Hydrophilidae	L	L=P;A=CG	P	P
8	Coleoptera	Hydrophilidae	Hydrophilidae	A	L=P;A=CG	CG	CG
9	Coleoptera	Hydrophilidae	Berosus	A	L=P;A=CG	CG	CG
10	Coleoptera	Hydrophilidae	Berosus	L	L=P;A=CG	P	P
11	Coleoptera	Hydrophilidae	Hydrocanthus	L	L=P/CG;A=P	P/CG	P/CG
12	Coleoptera	Hydrophilidae	Hydrocanthus	A	L=P/CG;A=P	P	P
13	Coleoptera	Haliplidae	Haliplidae	A	SHR/P	SHR/P	SHR/P
14	Diptera	Chironomidae	Endochironomus		SHR/CG/FC	SHR/CG/FC	SHR/CG/FC
15	Diptera	Chironomidae	Glyptotendipes		SHR/FC/CG	SHR/FC/CG	SHR/FC/CG
16	Odonata	Coenagrionidae	Argia	P			P

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E2 =IFERROR(INDEX(Sheet1!$A:$D,MATCH($C2,Sheet1!$C:$C,FALSE),4),"") F2 =IF(D2="",E2,IFERROR(MID(E2,SEARCH(D2&"=",E2)+2,FIND(";",E2&";",SEARCH(D2&"=",E2))-SEARCH(D2&"=",E2)-2),E2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

 

[nostradamus]

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? .

 

[Eric W]

Just add &"" to the end, like this:

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

 

[nostradamus]

Just add &"" to the end, like this:

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

Mucho Gracias!!

 

[Eric W]

De nada!