VLOOKUP - Multiple Rows

[cwhaley1]

Hi all. Long time reader, first-time poster...

I'm fairly competent at VBA as I've been on a few courses and spent a couple of years doing self-training. Unfortunately though, I'm limited on my use of formulae.

First of all, a bit of background to my area of work. I work for a US/UK retailer and I work with database information stored on store layouts. This information generally refers to areas within a store (Skincare, Food, Vitamins, etc.) Each of these areas are put into families, so Cotton Wool, Beauty Accessories and Make Up all come under the family of "Beauty Accessories". Those families are listed in a table which compares the existing store layout data to the proposed layout data -- the proposed layout being the result of a potential refit of the store.

As you can imagine, a store may have more than one instance of a family. For example, let's say our store has Cotton Wool and Make Up. This means there would be two entries for "Beauty Accessories". If Cotton Wool was increasing in size, but Make Up was staying the same, we would only need to order kit/stock for the Cotton Wool build. I mark the change as "Yes" (because there is change to this area) whereas Make Up would be marked as "No". In theory, I'd only want the Cotton Wool area pulled through to where the VLOOKUP is.

Now for the VLOOKUP itself... A separate tab in the spreadsheet breaks down the database information and would tell us what we need as a result of the increase for the Cotton Wool build. In order to pull through only the relevant builds, it filters to anything marked as "Yes" for change and returns the name of the area in a column. In theory, we should only see Cotton Wool pulled through and the relevant kit. The formula is: =VLOOKUP(D2187,'Master Output'!$B$7:$N$1000,10,FALSE)

In reality however, we get both of the "Beauty Accessories" areas pulling through, despite only one of the areas in this family being marked as "Yes". This problem seems to be because the VLOOKUP will only look at the first "Yes" and then take any other build below it as a "Yes" to.


My question... is there a formula which can check more than just the first entry in a list of rows? I want the formula to see "Beauty Accessories", check if there is a "Yes" and return the name of the area, but also check the next row to see if there is a "No" or a "Yes" marked against it.

Please let me know if you need any more information. I am so sorry for the mass of text!

Yours hopingly...

 

[estevaoba]

Hi!

I would try this array formula (paste and hit ctrl+shift+enter keys simultaneously):

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IFERROR(INDEX('Master  Output'!$B$7:$N$1000,SMALL(IF('Master Output'!$B$7:$B$1000=D2187,ROW('Master  Output'!$B$7:$B$1000)-6),ROW($A1)),10),"")[/TD]
[/TR]
</tbody>[/TABLE]

Change the $B$7:$B$1000 range to the correct column where D2187 is lokedup from, then array enter and copy down.

 

[cwhaley1]

Hi!

I would try this array formula (paste and hit ctrl+shift+enter keys simultaneously):

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(INDEX('Master  Output'!$B$7:$N$1000,SMALL(IF('Master Output'!$B$7:$B$1000=D2187,ROW('Master  Output'!$B$7:$B$1000)-6),ROW($A1)),10),"")[/TD]
[/TR]
</tbody>[/TABLE]

Change the $B$7:$B$1000 range to the correct column where D2187 is lokedup from, then array enter and copy down.

Really appreciate the quick reply here, thank you!

That's given me some progress. I now get values being returned from more than one line - which is a first - however it will only do it for the first two lines.

In the formula, the $A1 returns the first row, $A2 returns the second row and $A3 onwards returns a blank cell for the rest of the spreadsheet.

What is the A1 for?

Thanks again.

 

[estevaoba]

ROW($A1) is the K argument, for the SMALL function. ROW($A1)=1, i.e., the first row with pertinent data. As you copy down, it turns into ROW($A2)=2, the second row with pertinent data, and so forth.

It worked here for all rows as in your example table.

Please make sure you see those curly brackets {} enveloping in your formula, which means you have array entered correctly.
Otherwise, it will give you only the first row or an incorrect result.

 

[cwhaley1]

ROW($A1) is the K argument, for the SMALL function. ROW($A1)=1, i.e., the first row with pertinent data. As you copy down, it turns into ROW($A2)=2, the second row with pertinent data, and so forth.

It worked here for all rows as in your example table.

Please make sure you see those curly brackets {} enveloping in your formula, which means you have array entered correctly.
Otherwise, it will give you only the first row or an incorrect result.

Thank you very much for your help. It was the A1 / A2 section I was failing to get my head around, but understand that now.

Not sure how to thank/rate your comment, so thank you very much!

 

[estevaoba]

You're welcome!
I'm glad to help.
Take care!