Look up and return text values

  • Thread starter Thread starter plb2862
  • Start date Start date
P

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
 
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 said:
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
Click to expand...

Works like a charm. Thank you
 
Back
Top