Column Return Instead of Row with INDEX,SMALL,IF

[Critical Bounce]

Hi

I'm new to the Forum. For the most part I muddle by with Excel but I have definitely hit a brick wall and can't find a particular solution to my problem anywhere. So I've come to ask the more knowledgable people for an answer.

I have a very nice INDEX SMALL IF Function in my sheet that Checks two columns A and B with 380 rows for instances of a word in a cell and returns the value of another column C when that instance occurrs in that row, which is the following:

=INDEX($B$3:$D$382,SMALL(IF($C$3:$D$382=$Q$5,ROW($C$3:$D$382)),ROW(1:1))-2,1)

This repeats over 38 rows (as there will be 38 instances out of 380) with the second ROW function increasing 2:2, 3:3 etc. each time.

I am stumped at what to do now.

I need to figure out how, in a separate cell, to check the same rows and columns for the same instance of the word yet instead of returning the value of another column as before I need it to know which column it is in either A or B and not a corresponding cell value in column C.

I have tried all manner of COLUMN permutations instead of ROWS but I am now at a complete brick wall.

Any help would be massively appreciated.

[Tony Valko]

Post a SMALL sample file that shows us what results you expect.

A SMALL file will have about 20 rows worth of data.

[Critical Bounce]

HI Thanks.

Have attached a sample. The Area highlighted in yellow is the column that needs to reflect whether that team played away or home (i.e. which column did the team name occur in; column C or Column D).

Small Example.xlsx

Hope this makes a bit more sense?

Cheers

[Jacc]

Instead of ROW(1:1))-2 you can try ROWS($C$3:C3)).

[Jacc]

A few more adjustments here. If you only get values from the first column there is no need to give INDEX the full range.

Please Login or Register  to view this content.

[Critical Bounce]

Instead of ROW(1:1))-2 you can try ROWS($C$3:C3)).

Sorry Jacc. Thanks for the swift reply but it still only retrieves the corresponding date and not the column that the name appears in.

CB

[Jacc]

Not a very solid solution but I think this is correct.

[Jacc]

Sorry, that was far from correct.

[Critical Bounce]

I'm sure its something to do with returning the column number with COLUMN() instead of referencing the value in column B from the corresponding row. but not sure how to translate that into the proper syntax.

[Jacc]

Here is something more sturdy. And correct I hope.

Please Login or Register  to view this content.

Edit: Ironically it works without the COLUMN function.

[Critical Bounce]

Hi Jacc

That is working almost perfectly! Brilliant. One tiny problem is that the Home Team / Away Team returns are reversed. I'm trying to think of the section to correct this but still drawing a blank.

Great work though. Thank you.

CB

[Jacc]

Good to hear it works!
Not sure I understand your last problem. Just swap the contents of cells C1 and D1 or what do you mean?

[martindwilson]

maybe this

[Critical Bounce]

Ignore me I was putting it in the wrong team column. Fixed. Solved, Excellent!

Many thanks for your help, Jacc

[Jacc]

--------

[Jacc]

Good one, Martin!
I tried a similar approach first but couldn't get it right.

[Tony Valko]

I guess I'm no longer needed here.