Upward and Left Lookup

[andrewc]

Hi,

I have text in columns A, B and C. I am trying to create a formula in column D that will, for each item in C, return the first item in column B that is on the same row or on a row above it.

For example, consider C11. In D11 I would like a formula to return the first text it encounters when looking up column B from B11 to B1.

Can someone please suggest a solution.

Thanks!

[Bob Phillips]

Won't that always be the first value in B? Can you give some examples?

[andrewc]

Hi Bob,

Sorry that my post wasn't clear.

For example, cells B6:B11 are blank but "Hello" is in B4 and "Goodbye" in B5. The formula in D11 would look up column B from B11 and return the first text it encounters, in this case "Goodbye".

Thanks,

Andrew

[Andrew-R]

I can do this, but only with a helper column.

Assuming that your data starts in row 2, then in E2 put the fomula =IF(B2="","",ROW())

Now in cell D2 you can put the formula =IF(C2<>"",OFFSET($B$1,MAX(INDIRECT("E2:E"&ROW()))-1,0),"")

Copy the formulas in D2 and E2 down your sheet and see how it looks.

[Azam Ali]

try this array formula in D11 and confirm throgh Ctrl+Shift+Enter

=INDEX(B:B,LARGE(IF(LEN(C11)>0,IF(ISTEXT($B$1:INDIRECT("B"&ROW(C11))),ROW($B$1:INDIRECT("B"&ROW(C11))))),1))

and copy down

or use following in D11 not to show error or zero

=IFERROR(INDEX(B:B,LARGE(IF(LEN(C11)>0,IF(ISTEXT($B$1:INDIRECT("B"&ROW(C11))),ROW($B$1:INDIRECT("B"&ROW(C11)))),""),1)),"")

[andrewc]

Perfect, thanks very much

[Azam Ali]

If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

[DonkeyOte]

Why not simply ?

Please Login or Register  to view this content.

The above is non-volatile, non-array and uses Binary Search (fast).
The approach is flawed if the strings in Column B exceed 255 chars in length
(would require adaptation)

FWIW, re: below:

Please Login or Register  to view this content.

there's no need for INDIRECT, relative references suffice surely ?