Enter a specific value.

AlbertaTerry

New Member
I have a question about entering a particular value in a cell. I have the following worksheet:

In Cell Q7 (and subsequent rows in column Q), I want to scan Column B, find the value that matches Cell M7, then move along that row to Column I, and enter that value.

In Cell Q6, I used the OFFSET function =OFFSET(I6,7,0,1,1) but that only iterates by one row when I fill it down. To work, I need it to iterate by 7 rows.

Can anyone help me out?

Attachments

• Excel Entry Problem.JPG
122 KB · Views: 4

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Eric W

MrExcel MVP
Try this in Q7:

=VLOOKUP(M7,\$B\$4:\$I\$1000,8,0)

AlbertaTerry

New Member
Hello Eric - that is perfect! Thank you very much. I'm going to try figuring out why it's perfect; if I can't figure it out, I might have to ask you. Thank you once again.
Regards from Terry.

(I will also make note of the xl2bb tool)

Eric W

MrExcel MVP
You could have used a variation of the OFFSET you first tried, but I think the VLOOKUP makes more sense. By all means, let us know if you have follow-up questions.

As far as the XL2BB tool, it's a huge help for the helpers here, and you'll likely get quicker responses if you use it.

Anyway, glad I could help and good luck!

Replies
0
Views
105
Replies
9
Views
490
Replies
1
Views
243
Replies
2
Views
148
Replies
3
Views
68

1,148,284
Messages
5,745,846
Members
423,981
Latest member
ph1l

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.

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