Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using Excel 2010, I have a mysql query that populate a list. I think my scenario is quite classic.

In one column I have a label: SPEC, SWD, PRD, CLOSURE... etc Then I have 10 columns that have for headers the labels. Finally I have a column in which I'd like to substitute the label name by its value but I also 'd like it loop to the columns on the left until it finds a value.

Exemple:

in the first column label I have the value PRD

The column named PRD is empty

The column named SWD has the value 05/02/2013

The column named SPEC has the value 21/01/2013

I'd like my last column to show 05/02/2013

I'd like to avoid VBA to populate the list but I'm not entirely close to the idea

Thank you very much for your help!

Pierre-Louis

share|improve this question

1 Answer 1

up vote 0 down vote accepted

I understand from your question that you're basically looking for the first entry from the 10 left columns. Assuming your data is in columns B:K, the following formula will do the job:

=INDEX(B1:K1,MATCH(FALSE,ISBLANK(B1:K1),0)).

You need to enter it as an array formula, i.e. instead of pressing Enter, you need to press Ctrl-Shift-Enter (you'll see curly brackets around the formula afterwards).

share|improve this answer
    
I intend to combined the index and the match formulas but Isblank doesn' t retruns an array. I'm using a table so I need to use the notation of tables. The hard part for me is to start from my column and loop to the left until I find a value in a cell –  Pierre-Louis Jolicard Jan 16 '13 at 15:44
1  
Pierre-Louise - even in a table you should be able to use the normal references and array formulas. Pretty sure that formula will also work there. However, you can also easily use the table notation, i.e. =INDEX(Table1[@[SWD]:[col11]],MATCH(FALSE,ISBLANK(Table1[@[SWD]:[col11]]),0)) should do what you need. See this screenshot: imgur.com/XyfYk –  Peter Albert Jan 16 '13 at 17:26
    
Thank you very much! –  Pierre-Louis Jolicard Feb 5 '13 at 8:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.