Using ROW() Function with VLOOKUP?

[Southerncentralrain]

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.

 

[RoryA]

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]

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]

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]

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]

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:

=VLOOKUP(D3,Sheet2!$G$2:$H$245,2,FALSE)&G2

Assuming I did understand you correctly!

 

[Southerncentralrain]

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]

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.