Array formula to return text

**RicWir** · 03-18-2009, 05:25 PM

I have an Array formula that returns a value from several rows below:
=MAX(IF($P$8:$AQ$8="Today",$P13:$AQ13))

Row 8 contains only text and only one occurrence of the word "Today".
Row 13 has only numbers. The corresponding value directly below the column containing the word "Today" is returned successfully.

I now need a similar formula to return a text entry located in the same column but on the previous row (row 12) directly above the value just returned and this row contains only text entries.

Using the above formula and referencing $P12:$AQ12 returns 0 as expected.

**Ron Coderre** · 03-18-2009, 05:33 PM

Since "today" will only appear once in row_8
Try these formulas:
For the number:

Please Login or Register  to view this content.

For the text:

Please Login or Register  to view this content.

Does that help?

**RicWir** · 03-18-2009, 06:08 PM

The formula: #HLOOKUP("today",$P$8:$AQ$13,6,0)# will pose a problem in that there are 100 groups of data needing the same formula but only one header row which contains the word "today". It works great for the first group however. Thanks for such a quick reply!

Actually, I can make it work by changing the row_index_num at each group. I'm still open for other suggestions but this will get me through for now. Thanks Ron!

**daddylonglegs** · 03-18-2009, 06:30 PM

Perhaps INDEX/MATCH is more easily adaptable for you

=INDEX($P13:$AQ13,MATCH("Today",$P$8:$AQ$8,0))

just change the first range depending on the row from which you want to return data