Automate column filling after lookup

Please note the problem has changed...The new issue is in a post #6 below. Thanks!
Hello All,

I’m not sure if I need to use a lookup or a Macro.
I have a workbook that contains several columns on one sheetone of which notes “descriptions.” These descriptions vary. On another sheet there are several columns of “categories.”These columns have headers which is what the “descriptions on the first sheetare grouped under. How can I automate grabbing the “header” from sheet two intosheet one after a search is done for the “descriptions” in the individualcolumns on Sheet two?


I’m looking for a way to automatically fill column 5 withthe headers on sheet 2, (Dogs, cats, fish, pens, metal) IF the description in Column 4 on page one is anywhere on Sheet 2.
Please help or let me know if this is even possible. Thanks in Advance

Examples Attached

fill column.xlsx


Please note the problem has changed... can someone tell me how to edit the formula to retrun and actual error if the "description" isn't on the list in sheet 2? For some reason it defaults and chooses the last column I was in and enters that in the field if the description isn't found.

In your example
=INDEX(Sheet2!$A$1:$E$1,SUMPRODUCT((Sheet2!$A$2:$E$5=Sheet1!D2)*COLUMN($A$2:$E$2)))

SUMPRODUCT(((Sheet2!$A$2:$E$5=Sheet1!D2)*COLUMN($A$2:$E$2)) will return the appropriate column that the description occurs in (from 1 to 5)
Does that work for you?

Hi gigi79,

Try using below formula:-

Formula:

{=INDEX(Sheet2!$A$1:$E$1,1,MIN(IF(Sheet2!$A$2:$E$5=Sheet1!$D2,COLUMN(Sheet2!$A$2:$E$5),"")))}

Enter this using key combination ctrl shift enter..

see attached:- fill column.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

Try

=IF(SUMPRODUCT((Sheet2!$A$2:$E$5=Sheet1!D2)*COLUMN($A$2:$E$2))=0, "No Match", (INDEX(Sheet2!$A$1:$E$1,SUMPRODUCT((Sheet2!$A$2:$E$5=Sheet1!D2)*COLUMN($A$2:$E$2)))))
Would that work?