Im in over my head

[doveyp]

Can somene please help me with the following: if cell b16=any cell in the range f16:j16 then display the cell in column k that is in the same row as the match? If no match in the range please display nothing. Thankyou in advance.

 

[doveyp]

Hi Jason,

Thankyou the workaround works, much appreciated. I shall apply it to my large spreadhseet and see how we go. Thanks.

 

[doveyp]

When I change the data in b16 I still get a result even if B16 has no exact match in the data table range f15:j16. It seems to give me the closest match.

 

[jasonb75]

Try this version instead, needs to be array confirmed with shift ctrl enter as before.

=IF(OR($F$15:$J$16=B16),INDEX($K$15:$K$16,MIN(IF($F$15:$J$16=B16,(ROW($K$15:$K$16))))-ROW($K$15)+1),"")

 

[doveyp]

Hi Jason,

Obviously I was in over my head, thankyou.

 

[snoopyhr]

maybe this can do what you want as well, this just an alternative, if it works for you use good, if not, please ignore

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
16	blue				brown	red	green	blue	red	blue
17	purple				brown	red	green	blue	red
Sheet14