Returning a Row of data by matching a criteria in a Column [SOLVED]

[ianamck]

Hi Guys and Girls I have been an avid reader of both the forum and Chandoo's blog for quite a while now, and still continue to learn everyday something new. Now its my chance to ask a question.


I have sample table of data 6 columns by six rows. I have solved the problem of extracting a column of data by matching a criteria using an array formula


|apples |oranges|pears |plums |raisins

-----------------------------------------------

abby |101 |105 |110 |115 |120 |

-----------------------------------------------

beth |102 |106 |111 |116 |121 |

-----------------------------------------------

callie |103 |107 |112 |117 |122 |

-----------------------------------------------

dora |104 |108 |113 |118 |123 |

-----------------------------------------------

elaine |105 |109 |114 |119 |124 |

-----------------------------------------------


The ARRAY formula I used was


=OFFSET(Table,0,MATCH(D10,OFFSET(Table,0,0,1,COLUMNS(Table)),0)-1,ROWS(Table),1)


D10 being Oranges it returns the following


oranges

105

106

107

108

109


However I can not figure out for the life of me how to complete the formula to extract a row of data using a similar formula. So if I was to enter beth it would give me


beth |102 |106 |111 |116 |121 | for example


Any help would be appreciated


Ian M

 

[NARAYANK991]

Hi Ian ,


Try this :


=OFFSET(Table,MATCH(A10,INDEX(Table,0,1),0)-1,1,1,COLUMNS(Table)-1)


entered as an array formula , using CTRL SHIFT ENTER. Select a range of 1 row and 5 columns e.g. B10:F10 , and enter the above formula. A10 contains the name of the person e.g. abby , beth , callie ,...


Narayan

 

[ianamck]

Narayan you are an obvious genius and so quick. When you see the answer it make sense straight away. Sometimes you can't see the wood for the trees. Thank you very much for such a rapid response.


Kudos to Narayan


Ian M