VLookup Formula

[chemicalx]

So wife does RE work….column A has names of countries. column B has a result like home, remote, hybrid. column A may list a city 2x because it has more than 1 column B result. ie, China might be in column A 2x, with 1 result as home and a second result of hybrid.

On another tab she is listing each city from column A and VLookup formula is pulling in a column B result from the other column B…..but say she types China, the VLookup only pulls in 1 column B result, like hybrid. how can she get that column B to pull in everything listed for chine from the first tab (say home and hybrid)?

thx

 

[Automatrix]

Power Query: Merge queries, then expand table.

 

[keef2]

Can you provide a sample of your data with Xlb22? Or even a screen shot or something?

 

[chemicalx]

Power Query: Merge queries, then expand table.

Thx for the reply, but we have no idea what Power Query is or how to use it.

 

[keef2]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[chemicalx]

Then she has another tab, she said she is using VLookup, but not sure......but here is what she has;

So, when she types China into the 2nd tab or VLookup tab, she wants column B next to China to return not just home, but work and static into B1. Right now, it only brings in 1 value, but there might be other values.

Make better sense?

 

[Automatrix]

There are numerous videos and sites that describe how to get started. It's easier than it sounds, you won't regret spending some time exploring the possibilities.

Here's a random example to get you started: Power Query Tutorial - What is it, How to use, Full examples, Tips & Tricks

 

[keef2]

Not sure of the format you want to use and if you are only using 2016 FILTER function doesnt work so here is one option:

Book1
	A	B	C	D	E
1	China	Home		China	Lookup
2	China	Work		China	Home
3	China	Static		China	Work
4	India	Home		China	Static
5	Egypt	Static
6	Egypt	Home
7	US	Home
8	Israel	Home
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,(ROW(A:A)-ROW($A$1)+1)/(ISNUMBER(MATCH(A:A,$D$1,0))),ROWS(D$2:D2))),"")
E2:E8	E2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,(ROW(A:A)-ROW($A$1)+1)/(ISNUMBER(MATCH(A:A,$D$1,0))),ROWS(E$2:E2))),"")

If you do have filter function here is another option:

Book1
	D
10	China
Sheet2

Cell Formulas
Range	Formula
D10:E12	D10	=FILTER(A:B,A:A=D1)
Dynamic array formulas.

 

[keef2]

Here maybe another option:

Book1
	A	B	C	D	E	F
1	China	Home		China	Home,Work,Static
2	China	Work		India	Home
3	China	Static		Egypt	Static,Home
4	India	Home		US	Home
5	Egypt	Static		Israel	Home
6	Egypt	Home		0
7	US	Home
8	Israel	Home
Sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=UNIQUE(A:A)
E1:E5	E1	=TEXTJOIN(",",TRUE,IF(D1=A:A,B:B,""))
Dynamic array formulas.

 

[chemicalx]

Here maybe another option:

Book1
	A	B	C	D	E	F
1	China	Home		China	Home,Work,Static
2	China	Work		India	Home
3	China	Static		Egypt	Static,Home
4	India	Home		US	Home
5	Egypt	Static		Israel	Home
6	Egypt	Home		0
7	US	Home
8	Israel	Home
Sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=UNIQUE(A:A)
E1:E5	E1	=TEXTJOIN(",",TRUE,IF(D1=A:A,B:B,""))
Dynamic array formulas.

gonna give this a whirl