Capturing multiple HLOOKUP results in an array

**tnfire** · 02-26-2009, 03:27 PM

I was provided previously with the code for a VLOOKUPS function for a project of mine by JBeaucaire. It can be found here, in the returned file he provided to me: VLOOKUPS multiple results in a drop down list

The set up works wonderfully. But now I'm trying to accomplish something that is just a little bit different. I'm trying to to populate an array with all the results returned from an HLOOKUP formula. I was wondering if anybody had any code written that might create an HLOOKUPS function. I've tried to tweak the code provided for the VLOOKUPS function by JBeaucaire but I'm a novice, novice programmer and can only pull off simple functions and procedures.

**JBeaucaire** · 02-26-2009, 07:59 PM

Let's see what you're working on. Post it up.

**tnfire** · 02-26-2009, 08:41 PM

Okay, here it is.

There are three sheets and they are all pretty simple. On the sheet 'Tag Data Input' column B is already configured. Column D will be used if a product cannot be pulled up by either Column B or C, and it is manually entered data. I am going to be pulling the data from a complete Inventory list.

The inventory that is pulled for 'Tag Data Input' sheet Column B is based on what my company has posted on our price list and in our personal inventory. The Inventory that Column C will be based on is not only our stuff, but everyone else’s inventory that we carry.

On the sheet 'Tag Data Input' cell C3 needs to be drawn from an array in Column C of the 'Relay' sheet. Much like cell B3 is drawn from the array that I created in column B of the 'Relay' sheet, using your method and the VLOOKUPS function that you coded.

But the array I need to make is an HLOOKUP with multiple results. I need all the results, around 1550, to be listed based on the lookup for 'Relay' cell C1 "Description". I'll then create a drop down box and implement it on the first sheet in cell C3.

The sheet 'Tag' is where the appropriate information is dumped in a printable format.

Sorry if that was confusing at all, I'm sure looking at it would probably have been sufficient. But I'll post it now that if written it, in case it offers any clarification.

**JBeaucaire** · 02-27-2009, 12:48 PM

I've gotten the range connected for your cell C3 (Varieties from Inv), but I'll need to see the data to help with your horizontal array of values.

**tnfire** · 02-27-2009, 06:34 PM

This has been solved. While I clarified what I was asking for JB helped show me that I was looking at this from an unnecessarily complicated angle. The list I was looking to populate did not need an array that utilized an HLOOKUPS formula to return multiple results. In fact I was approaching the concept of an HLOOKUP formula incorrectly.

All that was needed was a simply ='Sheet2!'C1 formula, copied down, that then has an Advanced filter applied to it which eliminates repeating entries and copies the new list into column D.

After I set that up JB provided a Macro which would apply the advanced filter and copy it over to column D every time I open the workbook and the figures update on the dynamically changing list in column C. This has helped me finish designing my workbook exactly as I intended to.

JBeaucaire, thank you again for all of your help.