# A tweek on this formula

#### Fisher1975

##### Board Regular
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?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.

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

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.

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.

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

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

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

Replies
3
Views
135
Replies
5
Views
111
Replies
11
Views
568
Replies
7
Views
214
Replies
6
Views
189

1,196,250
Messages
6,014,240
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back