|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 15th, 2009, 09:14 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
How to use an array formula for tables
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. 
|
|
#2
May 15th, 2009, 09:34 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use an array formula for tables
=INDIRECT(ADDRESS(4,MIN(IF(B3:F40=G9;COLUMN(B3:F40 ),""))))
CTRL+SHIFT+ENTER it as this is an array-formula HIH On 15 Maj, 10:14, Dingerz wrote: 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.
|
|
#3
May 15th, 2009, 09:41 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use an array formula for tables
corrected
=INDIRECT(ADDRESS(4,MIN(IF(B3:F40=G9,COLUMN(B3:F40 ),"")))) On 15 Maj, 10:34, Jarek Kujawa wrote: =INDIRECT(ADDRESS(4,MIN(IF(B3:F40=G9;COLUMN(B3:F40 ),"")))) CTRL+SHIFT+ENTER it as this is an array-formula HIH On 15 Maj, 10:14, Dingerz wrote: 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.- Ukryj cytowany tekst - - Pokaż cytowany tekst -
|
|
#4
May 15th, 2009, 10:27 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use an array formula for tables
=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 --------------- Jacob Skaria "Dingerz" wrote: 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.
|
|
#5
May 15th, 2009, 10:46 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use an array formula for tables
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 --------------- Jacob Skaria "Jacob Skaria" wrote: =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 --------------- Jacob Skaria "Dingerz" wrote: 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
