Lookup

  • Thread starter Thread starter aresar
  • Start date Start date
A

aresar

I am wondering if there is a way to look up a number depending on wha
is above it.

the numbers in column A cannot be re-ordered in this case.

1 134
2 156
3 456
4 344
(blank row)
2 743
5 390
6 132
(blank row)
2 29
7 18
8 56

I want the function to get the value from B for the number 2, but onl
if there is either a number or a letter in the cell above it. So i
this case it would get the number 156 for an answer.

Any help would be appreciated
 
aresar,

=SUMPRODUCT((A2:A12=2)*(A1:A11<>"")*B2:B12)

Note that the ranges in the formula are offset by one row. Also, this only
works if there is only one 2 that is below a non-empty cell. Otherwise, it
will return the sum of all instances.

HTH,
Bernie
MS Excel MVP
 
Assuming your list starts at A2, try this. It works, but
your description is a little vague, so not sure if the is
the results your looking for. You would enter this
formula in cell C2 and copy it down. I would only extract
the numbers from the cells that have the number 2 in
column A and if the cell directly above the row in column
B contains data.

=IF(AND(A2=2,NOT(ISBLANK(B1))),+B2,0)
 
Back
Top