Index match column lookup

[collegeitdept]

Hi,

I'm trying to write a formula, either Index match, or sumproduct, that will look for a value (find a store location number) then give me its category (first value in its respective column)).

IE:


Blue Green Red Yellow White
107 104 101 102 401
117 110 103 105 402
118 114 111 106 403
129 121 116 115 404
134 122 119 120 405
136 126 123 137 406
162 128 135 152 407
174 130 142 154 408
175 131 143 157 409
1163 141 151 163 410
148 154 167 412
159 170 168 414
173 171 415
177 416
3081 417


(notice that green is actually a merged column)... i CANNOT unmerge it because it is an exported list from a database... in fact there are multiple merged columns but they do not at all affect the data below.


Example:
Store: 116
Result (Category): Red

Thanks.

 

[Haseeb Avarakkan]

Try,

H2 = Store, 116
A1:F1 = Category,
A2:F16 = Data

=INDEX(A1:F1,SMALL(IF(A2:F16=H2,COLUMN(A1:F1)-MIN(COLUMN(A1:F1))+1),1))

Must hit CTRL+SHIFT+ENTER, not just ENTER.

 

[collegeitdept]

Thanks so much.

It worked.