Lookup & Return Range of Data

I have tried everything and cannot figure this out......

I need to look up text in a range of data, and when a match is found,
have Excel return the data in the 11 cells to the right of the match.
It would be best if the match cell could be returned as well (making a
total of 12 horizontal cells, e.g., A4:L4).

Example:

My formula will be entered in cell A2 of sheet 1.
My data table will be in A5:L20 of sheet 2.

Formula will say "if sheet 2 cell A5 is xxx, then return the contents
of cells A5 THROUGH L5".

I hope I am being clear. I've tried everything I can think of -
vlookup, match, index. I can't figure out how to return the data from
a range of cells. Help! Thank you.

In A2 put

=IF(ISNA(VLOOKUP($A1,Sheet2!$A$5:$L$20,COLUMNS($A$1:A1),0)),"no
Match",VLOOKUP($A1,Sheet2!$A$5:$L$20,COLUMNS($A$1:A1),0))

copy across to L2, replace A1 with either the value you looking up of a cell
where you put the same value

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"shehasclass" <[email protected]> wrote in message
news:[email protected]...
>I have tried everything and cannot figure this out......
>
> I need to look up text in a range of data, and when a match is found,
> have Excel return the data in the 11 cells to the right of the match.
> It would be best if the match cell could be returned as well (making a
> total of 12 horizontal cells, e.g., A4:L4).
>
> Example:
>
> My formula will be entered in cell A2 of sheet 1.
> My data table will be in A5:L20 of sheet 2.
>
> Formula will say "if sheet 2 cell A5 is xxx, then return the contents
> of cells A5 THROUGH L5".
>
> I hope I am being clear. I've tried everything I can think of -
> vlookup, match, index. I can't figure out how to return the data from
> a range of cells. Help! Thank you.
>

Thank you Peo. This worked for the first row (A2), but when I copy the
formula down, each row returns the same info, rather than returning the
info from the row where it is finding my value.

Is this something to do with the absolute reference in this part of the
formula: COLUMNS($A$1:A1) ?? The "A1" will change to "B1", etc., of
course, as I copy it down, but the data returned is still the info from
line A.