| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return value 1 row above non-blank cell in range
Hi everyone,
I've been searching through functions but to no avail. I would like to find a way to search a range to find a cell with a value in it, then I would like the value above to be returned. e.g if range is B9:F9 and the cell with a value in is B9 then I would like the value in B8 returned. I thought I could do it in stages using MAX to find value then Index and Match to find cell ref (Duh!) and then OFFSET to return value above. Please help! Cheers -- Mifty |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return value 1 row above non-blank cell in range
One way:
It's hard to tell what you're searching for, but if you're looking for 'x', then =INDEX(B8:F8,MATCH(x, B9:F9, FALSE)) In article , Mifty wrote: Hi everyone, I've been searching through functions but to no avail. I would like to find a way to search a range to find a cell with a value in it, then I would like the value above to be returned. e.g if range is B9:F9 and the cell with a value in is B9 then I would like the value in B8 returned. I thought I could do it in stages using MAX to find value then Index and Match to find cell ref (Duh!) and then OFFSET to return value above. Please help! Cheers |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return value 1 row above non-blank cell in range
Assuming that one cell in B9:F9 is non-blank, then:
=OFFSET(B8,0,MATCH(LOOKUP(99^99,B9:F9),B9:F9)-1) will return the value in the cell above it. -- Gary''s Student - gsnu200785 "Mifty" wrote: Hi everyone, I've been searching through functions but to no avail. I would like to find a way to search a range to find a cell with a value in it, then I would like the value above to be returned. e.g if range is B9:F9 and the cell with a value in is B9 then I would like the value in B8 returned. I thought I could do it in stages using MAX to find value then Index and Match to find cell ref (Duh!) and then OFFSET to return value above. Please help! Cheers -- Mifty |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return value 1 row above non-blank cell in range
Hi JE,
I'm looking for values 1-5. I've just tried Gary's Student's solution and it does the trick but I've copied and pasted your formula for future learning. Many thanks -- Mifty "JE McGimpsey" wrote: One way: It's hard to tell what you're searching for, but if you're looking for 'x', then =INDEX(B8:F8,MATCH(x, B9:F9, FALSE)) In article , Mifty wrote: Hi everyone, I've been searching through functions but to no avail. I would like to find a way to search a range to find a cell with a value in it, then I would like the value above to be returned. e.g if range is B9:F9 and the cell with a value in is B9 then I would like the value in B8 returned. I thought I could do it in stages using MAX to find value then Index and Match to find cell ref (Duh!) and then OFFSET to return value above. Please help! Cheers |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return value 1 row above non-blank cell in range
Wow, thank you Gary's Student
Any chance you could explain how it works please? -- Mifty "Gary''s Student" wrote: Assuming that one cell in B9:F9 is non-blank, then: =OFFSET(B8,0,MATCH(LOOKUP(99^99,B9:F9),B9:F9)-1) will return the value in the cell above it. -- Gary''s Student - gsnu200785 "Mifty" wrote: Hi everyone, I've been searching through functions but to no avail. I would like to find a way to search a range to find a cell with a value in it, then I would like the value above to be returned. e.g if range is B9:F9 and the cell with a value in is B9 then I would like the value in B8 returned. I thought I could do it in stages using MAX to find value then Index and Match to find cell ref (Duh!) and then OFFSET to return value above. Please help! Cheers -- Mifty |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup to return blank cell if nothing in range | Excel Discussion (Misc queries) | |||
| Return cell adress for next non-blank cell in a range | Excel Worksheet Functions | |||
| Sumif to return a blank if sum range is blank | Excel Worksheet Functions | |||
| find the first blank cell in a range and return me it's position | Links and Linking in Excel | |||
| if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions | |||
Linear Mode
