Look up and return text values
 

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

Look up and return text values
 

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(Blan k!$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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"plb2862" wrote in message
...
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

Look up and return text values
 

"plb2862" wrote in message
...
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

Look up and return text values
 

Welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"plb2862" wrote
Works like a charm. Thank you