How to use an array formula for tables

  • Thread starter Thread starter Dingerz
  • Start date Start date
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.
 
=INDIRECT(ADDRESS(4,MIN(IF(B3:F40=G9;COLUMN(B3:F40),""))))

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

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

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

HIH
 
=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
 
=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
 
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
 
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
 
Back
Top