Report

Formula that searches in a row for a cell/column that contains [Solved]

Ask a question Zellie - Latest answer on May 12, 2016 04:47AM
Hi.
It would be greatly appreciated if someone could assist me with the following formula in excel:
I would like a formula that searches in a row for a cell/column that contains a number (which column in that row) and then use/copy the number in the second cell of that same column. A specific row will always only have one column with a number in.
For instance:
Search row B11 to G11 for a cell/column with a number. If for instance the number is in cell G11. Then copy the number in cell G2 to A11. IF the number is in D11, then copy D2 to A11. There will always only be one cell/column in the row that contains a number.
I used this formula but it does not search in which column the number is and then use that column.
=IF(ISNUMBER(G11),G$2,"")
9990-000 1100-000 1500-000 1200-000 1300-000
Formula
in this
column
1100-000 3
9990-000 8
1500-000 11
1300-000 10
I don't know if such a formula is even possible but will greatly appreciate any help.
Regards
See more 
Helpful
+0
moins plus
Zellie, Good evening.

Suppose:

B2 = 10
C2 = 20
D2 = 30
E2 = 40
F2 = 50
G2 = 60

AND...

A11 --> =IFERROR(INDEX(B2:G2,MATCH(LARGE(B11:G11,1),B11:G11,0)),"")

Now, try to put some number at any cell from B11 to G11

Please, tell us if it worked for you.
I hope this helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Zellie- May 12, 2016 04:47AM
Thank you so much. This worked 100%.
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!