Index Match

[How_Do_I]

Hello all, I can’t work out how to get the desired results in I4 and J4… My search is in cell H4 (“WM (44) 1”), Column E has two matches, one match WITH “Annex” in Column F and one match WITHOUT “Annex” in Column F… How would I get my desired results please…?


Sheet1

	D	E	F	G	H	I	J
3						Without Annex	Annex
4	CAB 65/45/1	WM (44) 1	Annex		WM (44) 1	CAB 65/41/1	CAB 65/45/1
5	cab 129/109	C 51-100			A22	cab 129/11
6	cab 129/11	A22
7	CAB 65/41/1	WM (44) 1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:82px;"><col style="width:69px;"><col style="width:64px;"><col style="width:64px;"><col style="width:69px;"><col style="width:102px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

<strike></strike>

<strike></strike>

 

[steve the fish]

Try this:

=IFERROR(INDEX($D$4:$D$7,MATCH(1,INDEX(($E$4:$E$7=$H4)*($F$4:$F$7=IF(I$3=$J$3,$J$3,"")),0),0)),"")

 

[How_Do_I]

That formula works steve, thanks for that. Unfortunately I don't understand it enough to add a third column which I've now noticed some of my data has...! How would I adjust the formula please..?

Excel Workbook
	D	E	F	G	H	I	J	K	L
3								Annex	Notebook
4	CAB 65/45/1	WM (44) 1	Annex			WM (44) 1	CAB 65/41/1	CAB 65/45/1	cab 129/11
5	cab 129/109	C 51-100				C 51-100	cab 129/109
6	cab 129/11	WM (44) 1		Notebook
7	CAB 65/41/1	WM (44) 1
Sheet1