Duplicate values showing in an INDEX/MATCH function

[dannyboy194]

Hi,
I have the following index/match function

=INDEX(Main!C4:C18,MATCH(Sheet1!E4,Main!F4:F18,0))

however, when there is only one option the cells which this formula are in just repeat the one option. so say the only option it can display is Tony and that formula is in seven cells it will display Tony in each of those cells. What I was wondering is if there is a way to only display "Tony" once rather than in the seven cells, so if the criteria is not met nothing is displayed?

Thanks
Dan

 

[Robert Mika]

Hi Denny,
You are using cell E4 to look at Column F to retrive data from column C.
What else woudl you expect to return if this bit (as I assume) Sheet1!E4 is alway the same?

 

[dannyboy194]

I know which is why I would like a formula that only returns the value once rather than finding the same value and repeating it. I'm not sure how to achieve this. So for example say the formula is in the individual cells from a1:a10 and say it only finds one result, "Tony", not to repeat it from a1:a10 but rather just once in a1?

Any help would be appreciated,
thanks
Dan

 

[Robert Mika]

IS this the same formula in all cells A1:10?

Whereis your lookup table and what's the values there?

 

[dannyboy194]

In Sheet 1 where the formula is I have the following headings


ID Name Surname Gender Current Year


the formula is in the Name column and it looks at the current year colum

it also looks in the "main" sheet

which has the following layout


Count if ID Name Surname Gender Current Year


it looks in the name and current year columns to make sure they match

i hope that helps?