A tweek on this formula

[Fisher1975]

Hi

I need to tweek this formula:

=LOOKUP(2,1/($A$2:$A2=$A1),$B$2:$B2)+1

All I need to do is make the red items 1 less than the row I paste the formula into

e.g. paste in row 128, they will equal a127 and b127

Any ideas?

 

[Love4Excel]

If you type this inside the correct cells, doesn't dragging it behind the other cells work? Normally this should do the trick and it's the easiest way.

 

[Fisher1975]

I dont want to type this in, I want to be able to copy this formula to any line and have it return some values from above

if I could do it your way I would, just trying to make something easy for non excel users

 

[Robert Mika]

I dont want to type this in, I want to be able to copy this formula to any line and have it return some values from above

if I could do it your way I would, just trying to make something easy for non excel users

What are you trying to achive?
This formula is written to look for last values from one column based on condition and return the value from the other.

 

[Love4Excel]

It's not typing. You just take what you got and drag it down or left or right. Whereever your values are. It's a standard excel function, the very basics, and it works like a charm. No need to type.

 

[Fisher1975]

What are you trying to achive?
This formula is written to look for last values from one column based on condition and return the value from the other.

Thats exactly what I am trying to do, just moved a few things around and got it all working now, thanks for the reply

 

[Fisher1975]

It's not typing. You just take what you got and drag it down or left or right. Whereever your values are. It's a standard excel function, the very basics, and it works like a charm. No need to type.

Thanks for the basics lesson

 

[Fisher1975]

What are you trying to achive?
This formula is written to look for last values from one column based on condition and return the value from the other.

I apologise it doesnt work.

Here is what I am trying to do:

I have a list of tasks which are numbered
e.g.
RPS 81
SET 91
RPS 82

Now depending on what I select in column A, I want it to return the next number, if RPS is selected I want to see 83.

The problem arises as I have set up a macro to copy the desired row (Standard row with more formulas) and paste it at the bottom of the list, where ever I put this row the lookup fields dont work which is why I want it to be the row above.

This is a spreadsheet for non excel people so I am trying to make it as easy to use as possible, in the past new lines have been incorrectly entered etc, you all must know how spreadsheets can get when used by non excel people

I hope I made sense