Look up and return text values

[plb2862]

Hi,
I have two tabs in workbook
Tab "Blank" which contains a table with two columns "A" & "B" and rows
"42:69." In column A you place an "x" to signify the text in column B is to
be used. At the top of the table I have a row that does Autofilter lookups.
Insufficient.

In Tab B there is a lookup cell which contains a formula
=VLOOKUP("x",Blank!A42:B69,2,0)
This works well to get well to get the first row with an "x" and the text to
the right.
=VLOOKUP("x",Blank!A42:B69,2)
This gets me the last row with an "x" and the text to the right.

What I'd like to do is get the text to the right in all rows that contain an
"x". So out of twenty-seven rows if three rows contain an "x" in column "A"
then the return would be the text in cell B row#? + B row#?? + Brow#???.

That lookup cell is one that is used with mailmerge.

Thanks

 

[Max]

One way ..

In sheet: Blank,
Put in C42: =IF(A42="x",ROW(A1),"")
Copy down to C69

Then in sheet: B,
Put in say, A2:
=IF(ROW(A1)>COUNT(Blank!$C$42:$C$69),"",INDEX(Blank!$B$42:$B$69,SMALL(Blank!$C$42:$C$69,ROW(A1))))
Copy A2 down by 28 rows to A29. A2:A29 will extract the required results
from "Blank", all neatly bunched at the top

 

[plb2862]

Hi,
I have two tabs in workbook
Tab "Blank" which contains a table with two columns "A" & "B" and rows
"42:69." In column A you place an "x" to signify the text in column B is
to be used. At the top of the table I have a row that does Autofilter
lookups. Insufficient.

In Tab B there is a lookup cell which contains a formula
=VLOOKUP("x",Blank!A42:B69,2,0)
This works well to get well to get the first row with an "x" and the text
to the right.
=VLOOKUP("x",Blank!A42:B69,2)
This gets me the last row with an "x" and the text to the right.

What I'd like to do is get the text to the right in all rows that contain
an "x". So out of twenty-seven rows if three rows contain an "x" in
column "A" then the return would be the text in cell B row#? + B row#?? +
Brow#???.

That lookup cell is one that is used with mailmerge.

Thanks

Works like a charm. Thank you

 

[Max]

Welcome ..