H Lookup in different folders - display whole column

**Simon1990** · 10-08-2013, 12:40 PM

Hi there,

I am trying to do the following thing with Excel: I have a couple of folders in an Excel file and in these folders, I put company names. These companies are evaluated in their respective column, one after the other, in their respective folder.

Instead of browsing through these folders in search of the right company to see the results, I would just like to enter any company name in a separate folder (a sort of interface) and afterwards, Excels searches with an H Lookup in the different folders until he finds the company name in the horizontal and gives me all the information from the respective column.

Two problems: Excel only wants to give me one specific value, e.g. from the cell 1 or 2 or 3. And Excel does not want to look in several folders for the company name.

I tried my luck among other formulas with the following one:

=IF(ISERROR(HLOOKUP(K13;'2'!B1:K2;2;WRONG));HLOOKUP(K13;'3'!B1:K2;2;WRONG));HLOOKUP(..

If the company's name were in this case in the second folder to look in for, Excel would only give the second value (but the aim stays the whole column..). But the research in the first folder does not work at all.

Or is there perhaps a totally different approach? E.g. by using macros?

For any kind of help, I would be quite grateful!

Thanks a lot,
Simon

**FDibbins** · 10-08-2013, 03:02 PM

Hi and welcome to the forum

I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**popipipo** · 10-08-2013, 03:13 PM

Change WRONG into FALSE
Maybe that wil help.

**FDibbins** · 10-08-2013, 03:47 PM

Good catch popip, I took that to be a range name, but a range name wont really fit there anyway

**Simon1990** · 10-08-2013, 08:25 PM

Hey,

thank you for your answers! I attached a sample file so that you can see what I am talking about.

So the worksheet one shall be the interface, where the result one a chosen company will be shown. The wanted company can be chosen in the dropdown list.

Then, a hlookup formula should scan the different work sheets for the company's name and should, when it found the company, not only display one value (like cell2 or 3), but the whole column of the respective company.

(in case if this is possible in Excel, I also want the evaluation scheme displayed, see dropdown list sector, because the criteria vary depending on the sector(=work sheets like finance, transport))

So thanks again for looking at it!!
Simon