Looking at multiple cells

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hello,

I would like to reference a cell for data, however if that cell is blenk
then look at the next cell, etc. For example:

In P1 I would like to reference cell F1, but if E1 is blank then the formula
looks at D1, and if D1 is blank I would like to look at C1, if C1 is blank I
would like to look at B1, and if B1 is blank I would like to populate P1 with
the data from A1. A1 will always have data, but the other cells may be blank.

Thanks in advance for the help.
Jim
 
If your A1 to F1 data is Numbers then use the below formula. Copy and paste
the below formula in P1 cell.
=LOOKUP(10^10,A1:F1,A1:F1)

If your A1 to F1 data is BOTH NUMBERS AND TEXTS OR TEXT then use the below
formula. Copy and paste the below formula in P1 cell.
=INDEX(A1:F1,MAX(COLUMN(A1:F1)*(A1:F1<>"")))
It is an array formula so it requires Cntrl+Shift+Enter. After pasting the
above formula in P1 cell place the cursor in P1 cell and press F2 and hit
Cntrl+Shift+Enter. Now in formula bar the formula will be surrounded by the
curly braces after hitting the Cntrl+Shift+Enter. General enter won’t work.

Remember to Click Yes, if this post helps!
 
Perfect - thanks!!!

Ms-Exl-Learner said:
If your A1 to F1 data is Numbers then use the below formula. Copy and paste
the below formula in P1 cell.
=LOOKUP(10^10,A1:F1,A1:F1)

If your A1 to F1 data is BOTH NUMBERS AND TEXTS OR TEXT then use the below
formula. Copy and paste the below formula in P1 cell.
=INDEX(A1:F1,MAX(COLUMN(A1:F1)*(A1:F1<>"")))
It is an array formula so it requires Cntrl+Shift+Enter. After pasting the
above formula in P1 cell place the cursor in P1 cell and press F2 and hit
Cntrl+Shift+Enter. Now in formula bar the formula will be surrounded by the
curly braces after hitting the Cntrl+Shift+Enter. General enter won’t work.

Remember to Click Yes, if this post helps!
Click to expand...
 
Back
Top