Index column from previous row with a value

**prudential** · 03-29-2018, 06:19 AM

Hi, how do you index a different column from previous row with a value?

https://drive.google.com/open?id=1NJ...-Vj5UIrner9fBS

The yellow cell (b6) is the last one with a value, how does one get(use) a6's value? The formulas will go in each cell in the b column to determine if its value is 'z' or blank.

Thanks.

**JohnTopley** · 03-29-2018, 06:52 AM

Try

=LOOKUP("zzzzzzz",$B$1:$B$100,$A$1:$A$100)

**prudential** · 03-29-2018, 07:05 AM

Hi John,

Thanks for your help.

If I change it to "z" and limit the range to B1:B6, A1:A6 it gives me the correct value in B7 but it gives me a circular reference warning when using B1:B100, A1:A100.

The formula will go in every cell in column B so it looks up the last (previous) row with a "z" in it.

Please note, the cells with "z" in them are just examples. There could be a 1000 rows of data with any number of cells in sequence containing "z". e.g there could be be 2 rows in a row with "z" then 2 blank cells then 1 cell with "z" then a blank cell and so on. The formula simply needs to find the previos row with a z and get the value from the corresponding A column.

Thanks

**JohnTopley** · 03-29-2018, 07:11 AM

Where are you placing the formula??

You cannot have both formula and value in B

**prudential** · 03-29-2018, 07:31 AM

The formula will go into every cell in B. Each B cell will look at the previous rows in B to see where the last cell contained a "z" and then lookup the adjacent A cell to use its value to determine if the B cell with the formula is a "z" or left blank. e.g B9 will look at all previous B rows (1-8) to see where the last cell with a "z" is. B20 will look at cells B1:B19. If the last "z" was in B15 then it will use A15's value to determine if B20 is a "z" or left blank.

**JohnTopley** · 03-29-2018, 08:05 AM

Please post a file.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**prudential** · 03-29-2018, 09:01 AM

Hi John,

That file is not an image, its an excel file that can be downloaded. I'm not sure how else to explain it, its quite straightforward.

Please let me know if you can help me.

P.S. Each B cell will contain an IF statement that looks for the previous B cell (B11)with a "z" value and uses the adjacent A cell's (A11)value to determine if the 'current' B cell (e.g. B15) is allocated a value of "z" when the condition is true or left blank when it is false.

Attached is new copy
https://drive.google.com/open?id=1P_...Y1WuPlzXV-qb_c

If we assume the IF statement and lookup/index formulas are already in B1:B11 giving a result for each B cell of "z" when true and blank when false.

**JohnTopley** · 03-29-2018, 11:46 AM

The "LOOKUP" either goes at the end of your range in column A or ie. A101/B101 and in any other cell (not in A/B)

**prudential** · 03-29-2018, 11:52 AM

Originally Posted by JohnTopley

The "LOOKUP" either goes at the end of your range in column A or ie. A101/B101 and in any other cell (not in A/B)

Hi John, thanks for your assistance with this.

What would be the formula in B15 to get the 9 value from A11 (based on the indexing finding the first "z")

P.S. The data flows down the page and looks back at previous cells, it never looks ahead. the B1:B1000 gives a circular reference warning. B15 should only look at B1:B14 for the first cell with a "z".

Isnt there a way to adjust the B1:B100 based on the current cell. E.g. If the formula is in B15 cant the lookup range be adjusted based on the B cell that contains the formula. e,g, B1: B(15-1) or something like that.

**JohnTopley** · 03-29-2018, 12:20 PM

If you put this in B2

=LOOKUP("zzzzzzz",$B$1:B1,$A$1:$A1)

and drag down (BUT remember you have you IF statement in B too!)

then you will override the formula with "Z" somehow (via your IF statement).

Sorry bu I really do not understand what you are trying to achieve.

EDIT: you would be better using Conditional Formatting with the LOOKUP formula to highlight the last A cell with "z" cell in B.

**prudential** · 03-29-2018, 12:31 PM

Brilliant, im pretty sure thats what I need. Why does the formula need 7 "z"?

It looks like the zzzzzzzzzz can be anything, its simply a placer for any text in the first cell it finds

**prudential** · 03-29-2018, 04:27 PM

Hi John,

Many thanks for your help with this. Just letting you know im still having issues, Its not looking up the correct cell. i.e. when im using multiple letters in the B column cells (e.g. cells with either "z" or "y", its not finding the right B cells and corresponding A cell values when i change the lookup text value

Any assistance would be greatly appreciated.

**JeteMc** · 04-04-2018, 02:47 PM

I don't understand the issue. I placed John's formula in cell B2 and copied down. I then placed 'z' in B1:B2. B3 displayed 15 as I expected. I placed 'y' in B4:B6 and B7 displayed 12... when 'w' was placed in B10:B11, B12:B18 displayed 9.
Let us know if you have any questions.