VLOOKUP and return string + INDIRECT relative to where initial VLOOKUP runs

[sarkocreme]

I have a table that looks like this:

refrigerator

=vlookup(randbetween(1,4),worksheet!$A1:$B4,2,false)

<tbody>
</tbody>

The vlookup where it's seeking (worksheet!$A1:$B4) this looks like:

1	"Buy a "&INDIRECT(ADDRESS(ROW(),COLUMN()-1))&" from us"
2	"Everything we sell is great"
3	"The best warranty ever"
4	Best "&INDIRECT(ADDRESS(ROW(),COLUMN()-1))

<tbody>
</tbody>

In the cases of rows #1 and #4 I want to return "refrigerator" for the INDIRECT formula. As written it will return "1" and "4" respectively since it runs relative to its local position. How can I run it local to the vlookup?

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​
1​	1​	Buy a # from us​		Refrigerator​	Buy a Refrigerator from us​
2​	2​	Everything we sell is great​		Car​	Best Car from us​
3​	3​	The best warranty ever​
4​	4​	Best # from us​

<tbody>
</tbody>

Formula in E1 copied down
=SUBSTITUTE(VLOOKUP(RANDBETWEEN(1,4),A:B,2,0),"#",D1)

M.

 

[sarkocreme]

Pretty slick - perfect!

 

[Marcelo Branco]

Glad it worked!

M.