Pull more than one record with vlookup? Or is this a Power Query procedure?

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

1603334557885.png


I've provided a screenshot on what I need help with.
In Column N and O I have names and numbers.
I did an Xlookup to get the names into column E.
Now I want to pull both numbers into column F from column O.
As you can see there is 0221 and 0222.
Is there a way to do this in Column F? There are times when I see multiple numbers under one name.
This would be A HUGE help if someone could help explain how to pull this off.
Thank you.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
one way to do this

Book1
ABCDE
1
2Name11Name11
3Name22Name14
4Name33Name66
5Name14Name67
6Name55Name68
7Name66
8Name67
9Name68
10Name99
11Name1010
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=INDEX($B$2:$B$11,SMALL(IF($A$2:$A$11=D2,ROW($A$2:$A$11)-(ROW($D$2)-ROW($D$1))),COUNTIF(D$2:D2,D2)))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I've provided a screenshot on what I need help with.
In future it would help your helpers greatly if you could post your sample data (& expected results) with XL2BB to make it easier by not having to manually type out sample data to test with. Hence I am also using AlanY's sample data.

Assuming that you have the FILTER function in your Excel 365, you could also try adapting this.

20 10 22.xlsm
ABCDE
1
2Name11Name11
3Name22Name14
4Name33Name66
5Name14Name67
6Name55Name68
7Name66
8Name67
9Name68
10Name99
11Name1010
12
Lookup All
Cell Formulas
RangeFormula
E2:E6E2=INDEX(FILTER(B$2:B$11,A$2:A$11=D2),COUNTIF(D$2:D2,D2))
 
Upvote 0
My apologies to you both. I should have added the XL2BB.
I will try this today and give an update, if these work both of you are amazing!
 
Upvote 0
When you give us the update, can you also share the column E formulas that you have used so we can understand how those values are obtained?
So I realize this is my company laptop and I can't download XL2BB but the formula is no longer necessary in column E.
I have a table in my ERP that will pull assortment and article for me.
Can you explain to me what your filter is doing?
I'm seeing an issue that if it's just one site, it will do the one site in my E column but then turn #NA.
I then did Ctrl + Shift + Enter but now it's just pulling the site from the very first assortment all together.
I think I'll be able to manipulate it but I just need to understand the formula better.
 
Upvote 0
Can you explain to me what your filter is doing?
For my sample data & layout the formula from cell E3 would be
=INDEX(FILTER(B$2:B$11,A$2:A$11=D3),COUNTIF(D$2:D3,D3))

FILTER(B$2:B$11,A$2:A$11=D3)
This returns an array of values from B$2:B$11 where A$2:A$11 = D3
That is, values from B$2:B$11 where A$2:A$11 = "Name1"
That would give {1,4}

So the overall formula becomes
=INDEX({1,4},COUNTIF(D$2:D3,D3))
=INDEX({1,4},COUNTIF(D$2:D3,"Name1"))
=INDEX({1,4},2)
=4
 
Upvote 0
For my sample data & layout the formula from cell E3 would be
=INDEX(FILTER(B$2:B$11,A$2:A$11=D3),COUNTIF(D$2:D3,D3))

FILTER(B$2:B$11,A$2:A$11=D3)
This returns an array of values from B$2:B$11 where A$2:A$11 = D3
That is, values from B$2:B$11 where A$2:A$11 = "Name1"
That would give {1,4}

So the overall formula becomes
=INDEX({1,4},COUNTIF(D$2:D3,D3))
=INDEX({1,4},COUNTIF(D$2:D3,"Name1"))
=INDEX({1,4},2)
=4
I really appreciate you breaking down the formula for me.
Huge help for my learning.
 
Upvote 0
I really appreciate you breaking down the formula for me.
Huge help for my learning.
I do have another question though.
With your Name 1 and Name 6 for instance, what if I don't want to write that out.
Is there a formula where it pulls those names from the A column without having to write down these in Column D?
Then I could that formula to pull the titles instead of the numbers, then use the code above to pull the numbers.
Is that possible?
 
Upvote 0
Is there a formula where it pulls those names from the A column without having to write down these in Column D?
I thought you already indicated that you had a formula for that:
I did an Xlookup to get the names into column E.
What is the logic for which names are drawn from column A and which names are not?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top