Index and Match?

  • Thread starter Thread starter j.ruderman
  • Start date Start date
J

j.ruderman

I have a workbook containing 14 sheets all structured the same. Cells E8:E39
may contain the text "PS Rep". When that text appears I need to extract the
text from the adjacent Column F8:F39. I'm tring to do this in a seperate
sheet, for the entire workbook, in one column without spaces.

Thanks
 
So what do you do if the text PS Rep appears on two sheets or more than once
in one sheet?
 
Hi,

If we assume that the entry can only occur once on any of the sheets you can
use something of the following form in 2007:

=IFERROR(VLOOKUP("PS Rep",Sheet2!E8:F38,2,FALSE),"")&IFERROR(VLOOKUP("PS
Rep",Sheet3!E8:F38,2,FALSE),"")

VLOOKUP doesn't work across 3D so you will need to concatenate a formula for
each sheet. In 2003 you could use:

=IF(ISNA(VLOOKUP("PS Rep",Sheet2!E8:F38,2,FALSE)),"",VLOOKUP("PS
Rep",Sheet2!E8:F38,2,FALSE))&IF(ISNA(VLOOKUP("PS
Rep",Sheet3!E8:F38,2,FALSE)),"",VLOOKUP("PS Rep",Sheet3!E8:F38,2,FALSE))

You could also write a custom VBA function.
 
Do your sheet names follow some kind of sequential naming pattern? Like
this:

Week1, Week2, Week3

June 2009, July 2009, August 2009
 
That is where i'm running into problems. The text may, or may not, appear
mulitple times per sheet and in mulitple sheets.
 
Somewhat I suppose...Su, Mo, and so on thru Sa, then the following 7 Sheets
are Su2, Mo2 and so on thru Sa2.
 
I just read your other reply where you say there may be multiple matches and
they could be on more than one sheet.

I can't think of an "elegant" way to do what you want. You may have to do a
separate lookup/extraction for each sheet.
 
Back
Top