I need a formula to search column B on multiple sheets for a name.
For every match I need it to return the values/names in columns C, D, & E of that row. I have a formula now that give me what I need from 1 sheet but I can't figure out how to do this over multiple sheets.
{=IF(ROWS(C$6:C8)<=$A$2,INDEX(LASSEN!E$2:E$1041,SMALL(IF(LASSEN!$B$2:$B$1041=$A$1, ROW(LASSEN!$B$2:$B$1041)-ROW(LASSEN!D$2)+1),ROWS(C$6:C8))),"")}
I have spent countless hours researching this and have tried tons of array lookup formulas with no luck. Any help would be greatly appreciated.
For every match I need it to return the values/names in columns C, D, & E of that row. I have a formula now that give me what I need from 1 sheet but I can't figure out how to do this over multiple sheets.
{=IF(ROWS(C$6:C8)<=$A$2,INDEX(LASSEN!E$2:E$1041,SMALL(IF(LASSEN!$B$2:$B$1041=$A$1, ROW(LASSEN!$B$2:$B$1041)-ROW(LASSEN!D$2)+1),ROWS(C$6:C8))),"")}
I have spent countless hours researching this and have tried tons of array lookup formulas with no luck. Any help would be greatly appreciated.