Cannot find a header when value repeats in a column

[kueva14]

Hello everyone,

I need to find headers from different sheets in column M:M. I came up with the formula but it fails when it discovers several repeating values in the same column.

What is missing that formula would return to a header even if the values are repeating in the column?

Any suggestion how to solve this is appreciated :)

[harrisonland]

How about trying this in M1 and copying down?

Please Login or Register  to view this content.

Does that do it for you?

Tim

[loginjmor]

Hi -

The problem with your formula is SUMPRODUCT will continue to add and multiply every match it finds. So then your column number grows with every match, way beyond the limit of your INDEX search array.

I suggest two things:

1. Instead of using multiple IF statements to select which Tab to go to, use the INDIRECT function to point the formula to the correct Tab automatically.

2. Use and IF statement as an Array formula to compare every number in your search array to the value you are looking for. Since the duplicates appear to always show up in the same column, simply take the MAX of the array that is created to find the column number. If there happens to be two columns with the same number, you will have to decide which one you want to use. In this case you will get the highest column number.

The formula I would recommend looks like this:

=IFERROR(INDEX(INDIRECT(H1&"!$B$1:$L$1"),,MAX(IF(L1=INDIRECT(H1&"!$B$2:$L$52"),COLUMN(INDIRECT(H1&"!$B$2:$L$52")),0))-1),"No Match")

For the Error message, I personally prefer "No Match" rather than " ", but that is up to you. It is easy enough to change.

I have attached your workbook with the above changes included.

Hope this helps.

[harrisonland]

Aaaaaand having re-read the question, I see I completely missed the point!

joginjmor's response, however, is spot on!

Tim

[JeteMc]

Please try the following in cell M1 and copied down:

Please Login or Register  to view this content.

This is a regular formula so just press the Enter key to activate.
Let us know if you have any questions.

[harrisonland]

NB: You can achieve the same with an adaptation of your own formula if you don't want to use an an array formula (with CTRL+SHIFT+ENTER) like this:

Please Login or Register  to view this content.

Tim