Search a Table, find, and copy all data from the row

[Toddman]

Would someone be kind enough to help me on this? I want to take a list
compare it to a table, and if a match copy all of the elements from th
table in the same row in which the element match was found:



LIST

CAT
DOG
MOUSE

TABLE

HORSE BROWN LARGE Worker
DOG WHITE SMALL Pet

When list matches table, I want to copy the information from th
following columns (e.g. WHITE, SMALL, Pet) and place in columns next t
the item in the list, so the list would then look like:

CAT
DOG WHITE SMALL Pet
MOUSE

Thank you for any help

 

[Nick Hodge]

Toddman

If there is only four columns in the lookup table (Say F1:I50) and you want
to return three, then select three cells to the right of the list (Say
B11) and enter

=VLOOKUP(A1,$F$1:$I$50,{2,3,4},false)

Enter this formula using Ctrl+Shift+Enter (Array formula). You will know if
you do this right as Excel will enclose the entire formula in curly brackets

Now copy down the length of the list. If it has #N/A! errors it is because
the function cannot find a match. This can be prevented but is another
question (Clue, ISNA Function)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Toddman]

Thank you Nick Hodge