Using ROW() Function with VLOOKUP?

Southerncentralrain

Board Regular
I am an amateur at excel and have just started to use the VLOOKUP function. I have a formula in the VLOOKUP original cell that is dependent on the row number of the destination cell. When I put the ROW() function in , it calculates the row number of the VLOOKUP original cell instead of the destination cell.

This is what is in the VLOOKUP original cell (on Sheet 2):

="Get prepared for game day or just a day out on the town with this "&INDIRECT("G"&(ROW()))&"."

I want it to put in the following in the destination cell of row 2 on Sheet 1 if it is called:

="Get prepared for game day or just a day out on the town with this "&G2&"."

I want it to put in the following in the destination cell of row 3:

="Get prepared for game day or just a day out on the town with this "&G3&"."

and so on.

* "&G2&" and "&G3&" will be the Title of the product name for each respective row.

Can anybody help with this? I have spent days on trying different things and searching around. Thanks so much in advance for your time.

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

RoryA

MrExcel MVP, Moderator
I'm afraid your question makes no sense to me. Where does VLOOKUP come into this? What values are you trying to get from where and to where?

Southerncentralrain

Board Regular
I am sorry. It is very confusing to me to explain since I am relatively new to excel.

VLOOKUP is looking at the product code and pulling the product description:

="Get prepared for game day or just a day out on the town with this "&INDIRECT("G"&(ROW()))&"."

The above is the product description.

This is for sports fan items and I am trying to make a spreadsheet so I am able to put in the product code and team ID and it fills everything in. I have everything working (e.g. title, SKU, etc.) except the product description.

Is there a way I can post the spreadsheet or email it to you? Thanks for your time.

RoryA

MrExcel MVP, Moderator
OK, so what is your actual VLOOKUP formula? If you want to post a workbook, you can use any of the many free filehosting sites (e.g. box.net) and post a link to the file once you have uploaded it there, or if you prefer send me a private message and I will give you my email address.

Southerncentralrain

Board Regular
The formula is =""&VLOOKUP(D3,Sheet2!\$G\$2:\$H\$245,2,FALSE)&""

- D3 is the Product Code
- On Sheet2 Column G is product Codes and Column H is their respective captions.
- An example of what is in Column H is:

="Get prepared for game day or just a day out on the town with this "&INDIRECT("G"&(ROW()))&"."

RoryA

MrExcel MVP, Moderator
Ah, OK I think I see what you want. You will need to approach it a different way as you cannot return the formula, only whatever value it has, and since you want the row to depend on the VLOOKUP cell, that won't work. You would need to change the source cell to just have the text (e.g. "Get prepared for game day or just a day out on the town with this ") and then concatenate the rest after the VLOOKUP:
Code:
``=VLOOKUP(D3,Sheet2!\$G\$2:\$H\$245,2,FALSE)&G2``
Assuming I did understand you correctly!

Southerncentralrain

Board Regular
Yes, I believe you understand it right. I was just hoping to be able to personalize it to each team name (i.e. instead of saying "Buy this great backpack now.", it would say "Buy this great Boston Celtics backpack now.").

I really appreciate your time. If you have any suggestions for this, I would love to hear them. Thanks again.

RoryA

MrExcel MVP, Moderator
That version should personalise it, if I've understood the layout correctly. I've just PMd you my email address in case you want to send me the workbook.

Replies
8
Views
936
Replies
5
Views
397
Replies
3
Views
460
Replies
61
Views
2K
Replies
4
Views
389

Threads
1,191,215
Messages
5,985,313
Members
439,957
Latest member
venky2002

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.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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