How to use an array formula for tables

D

Dingerz

Hello,

I dont really know how to explain what I want so I will try it in the best
way i can.

If Referenced Cell (G9) matches another cell in table (B3:F40) then make
current cell (the one i am writing the formula in) equal to Row 4 of the
particula column where the match was found.

In essence i want it to find some data in a table and tell me the header of
the column where it was found.
 
J

Jarek Kujawa

=INDIRECT(ADDRESS(4,MIN(IF(B3:F40=G9;COLUMN(B3:F40),""))))

CTRL+SHIFT+ENTER it as this is an array-formula

HIH
 
J

Jarek Kujawa

=INDIRECT(ADDRESS(4,MIN(IF(B3:F40=G9;COLUMN(B3:F40),""))))

CTRL+SHIFT+ENTER it as this is an array-formula

HIH
 
J

Jacob Skaria

=INDEX(B3:F40,1,MIN(IF(B3:F40=G9,COLUMN(B3:F40),""))-COLUMN(B3:F40)+1)

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula>}"


If this post helps click Yes
 
J

Jacob Skaria

=INDEX(B3:F40,1,MIN(IF(B3:F40=G9,COLUMN(B3:F40),""))-COLUMN(B3:F40)+1)

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula>}"


If this post helps click Yes
 
J

Jacob Skaria

If you mean row 4 of the table use

=INDEX(B3:F40,4,MIN(IF(B3:F40=G9,COLUMN(B3:F40),""))-COLUMN(B3:F40)+1)

OR if you mean row 4 of the sheet

=INDEX(B3:F40,2,MIN(IF(B3:F40=G9,COLUMN(B3:F40),""))-COLUMN(B3:F40)+1)


If this post helps click Yes
 
J

Jacob Skaria

If you mean row 4 of the table use

=INDEX(B3:F40,4,MIN(IF(B3:F40=G9,COLUMN(B3:F40),""))-COLUMN(B3:F40)+1)

OR if you mean row 4 of the sheet

=INDEX(B3:F40,2,MIN(IF(B3:F40=G9,COLUMN(B3:F40),""))-COLUMN(B3:F40)+1)


If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reference Formula Help 1
Finding Text within an Array 2
How to use vlookup in an array formula 2
Excel Formula for Eclipsing Circles 0
Referencing an array formula 2
Array Formula 1
Help with Cell Formula 3
Help with Cell Formula 4

Top