Excel formula help - need to lookup value 1

[Lynx03]

This may be an easy question but I'm having trouble getting this to work. I'm working in Excel 2000 and I've got an imported table that has 16 columns and a variable number of rows. Most of the time, each row has info in only one of the data columns (see below). I need to first determine if there is data in any of the columns and if so, how many columns are populated on that record - this I've got with this formula:

IF(COUNTA($K2:$Y2)<1,&quot;(blank)&quot;,IF(COUNTA($K2:$Y2)>1,&quot;(multiple issues)&quot

If there is only one column that has data, I need to return that value to another column on that row (see row_data below - I have no problem doing this part). The problem is that I also need to return the column header that the data came from to another column on that row (see col_data below).



[tab][tab][tab][tab]|[tab]c1[tab]|[tab]c2[tab]|[tab]c3[tab]|[tab]c4 [tab]||[tab]row_data [tab]|[tab]col_data[tab]|
[tab]--------------------------------------------------------------------------------------
[tab]row1[tab]|[tab][tab][tab]|[tab][tab][tab]|[tab]fg[tab]|[tab][tab][tab]||[tab][tab][tab]fg[tab][tab]|[tab][tab]c3[tab][tab]|
[tab]row2[tab]|[tab][tab][tab]|[tab]yx [tab]|[tab]df[tab]|[tab][tab][tab]||[tab][tab](>1)[tab][tab]|[tab] (>1)[tab][tab]|
[tab]row3[tab]|[tab][tab][tab]|[tab][tab][tab]|[tab] [tab]|[tab]bc [tab]||[tab][tab][tab]bc [tab]|[tab][tab]c4[tab][tab]|
[tab]row4[tab]|[tab][tab][tab]|[tab][tab][tab]|[tab] [tab]|[tab][tab][tab]||[tab][tab](<1)[tab][tab]|[tab] (<1)[tab][tab]|
[tab]row5[tab]|[tab]ft [tab]|[tab][tab][tab]|[tab] [tab]|[tab][tab][tab]||[tab][tab][tab]ft [tab][tab]|[tab][tab]c1[tab][tab]|


Hopefully this makes sense. Thanks in advance for any help! Email me if you'd like the original spreadsheet.

 

[KenWright]

Assuming your Row data is in Col Z and Your Col Data is in Col AA, then in cell Z2 put the following:-

=IF(COUNTA($K2:$Y2)<1,&quot;(blank)&quot;,IF(COUNTA($K2:$Y2)>1,&quot;(multiple issues)&quot;,HLOOKUP(&quot;zzzzz&quot;,$K2:$Y2,1)))

and in cell AA2 put the following

=IF(OR(Z2=&quot;(blank)&quot;,Z2=&quot;(multiple issues)&quot,Z2,INDEX($K$1:$Y$1,MATCH($Z2,$K2:$Y2,0)))

Then just copy both down.

Regards
Ken..................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SuicidED]

Hi possible answer

Row data formula =IF(B2&C2&D2&E2<>&quot;&quot;,A2,&quot;&quot
Column =IF(H2<>&quot;&quot;,B2&&quot;,&quot;&C2&&quot;,&quot;&D2&&quot;,&quot;&E2,&quot;&quot
Row data is in column H
Regards Eddie

 

[Lynx03]

Ken - worked perfectly!! Thanks so much!

 

[KenWright]

My pleasure, and appreciate the feedback.

Regards
Ken.............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------