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

[sarkocreme]

I have a table that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]refrigerator
[/TD]
[TD]=vlookup(randbetween(1,4),worksheet!$A1:$B4,2,false)
[/TD]
[/TR]
</tbody>[/TABLE]

The vlookup where it's seeking (worksheet!$A1:$B4) this looks like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]"Buy a "&INDIRECT(ADDRESS(ROW(),COLUMN()-1))&" from us"
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]"Everything we sell is great"
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]"The best warranty ever"
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Best "&INDIRECT(ADDRESS(ROW(),COLUMN()-1))
[/TD]
[/TR]
</tbody>[/TABLE]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

Buy a # from us​

[/TD]
[TD][/TD]
[TD]

Refrigerator​

[/TD]
[TD]

Buy a Refrigerator from us​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

Everything we sell is great​

[/TD]
[TD][/TD]
[TD]

Car​

[/TD]
[TD]

Best Car from us​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

The best warranty ever​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

Best # from us​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.