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
					Without Annex	Annex
WM (44) 1	Annex		WM (44) 1
cab 129/109	C 51-100			A22	cab 129/11
cab 129/11	A22
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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: #ffff00"]CAB 65/45/1[/TD]

[TD="bgcolor: #ffff00"]CAB 65/41/1[/TD]
[TD="bgcolor: #ffff00"]CAB 65/45/1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: #ffff00"]CAB 65/41/1[/TD]

</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