Need to compare two columns and fetch the matching values in a new column

[aghaffar82]

Kindly check the attached and advise how can this be achieved. Thank you in advance.

data for question.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			ABC	ABC	5	27.94
3	GHI	None	4	55.94			GHI	GHI	4	55.94
4	DMO	PPM	4	55.94			PQS	PQS	2	84.94
5	PQS	AGI	2	84.94			AGI	AGI	5	84.94
6	TTP	DGS	1	84.94
7	AGI	PQS	5	84.94
8	TOF	ABC	6	84.94
9
10
11	ORIGINAL DATA						DESIRED RESULT
12.11.20.dups (1)

 

[aghaffar82]

Yes, that's the mistake I made in my original post, I need to match item column (column B & pull the info for inv & price).

 

[Peter_SSs]

Yes, that's the mistake I made in my original post, I need to match item column (column B & pull the info for inv & price).

So, are you all sorted now?

 

[aghaffar82]

So, are you all sorted now?

Unfortunately No, the formula is still fetching values from column C & D based on the rows of column A and I need it to fetch them based on the rows of column B. Thank you

 

[Peter_SSs]

So, can you post a new small set of XL2BB sample data and expected results and explain carefully how you manually obtain one or two of the expected results?

 

[aghaffar82]

Yes Sir, here is the new range: The yellow columns are the original data and the light green columns are the desired results. I just don't want to use VLOOKUP and would prefer a dynamic solution instead. Thank you

data for question.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			ABC	ABC	6	84.94
3	GHI	None	4	55.94			GHI	GHI	5	27.94
4	DMO	PPM	4	55.94			PQS	PQS	5	82.62
5	PQS	AGI	2	84.94			AGI	AGI	2	84.94
6	TTP	DGS	1	94.55
7	AGI	PQS	5	82.62
8	TOF	ABC	6	84.94
9
10
Sheet1

Cell Formulas
Range	Formula
G2:H5	G2	=FILTER(INDEX(A:B,SEQUENCE(ROWS(A:B)),{1,1}),ISNUMBER(MATCH(A:A,B:B,0)),"No Data")
I2:I5	I2	=VLOOKUP(H2,B:D,2,0)
J2:J5	J2	=VLOOKUP(H2,B:D,3,0)
Dynamic array formulas.

 

[Peter_SSs]

Thanks for the new data & results.

Provided you are not concerned about the order of the results, this should do it with a single dynamic array formula in G2

aghaffar82.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			GHI	GHI	5	27.94
3	GHI	None	4	55.94			AGI	AGI	2	84.94
4	DMO	PPM	4	55.94			PQS	PQS	5	82.62
5	PQS	AGI	2	84.94			ABC	ABC	6	84.94
6	TTP	DGS	1	94.55
7	AGI	PQS	5	82.62
8	TOF	ABC	6	84.94
9
Match columns (2)

Cell Formulas
Range	Formula
G2:J5	G2	=FILTER(INDEX(A:D,SEQUENCE(ROWS(A:D)),{2,2,3,4}),ISNUMBER(MATCH(B:B,A:A,0)))
Dynamic array formulas.

However, if the order is important to you then with your existing dynamic array formula in G2, this one in I2 copied across to J2 produces the results you want.

aghaffar82.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			ABC	ABC	6	84.94
3	GHI	None	4	55.94			GHI	GHI	5	27.94
4	DMO	PPM	4	55.94			PQS	PQS	5	82.62
5	PQS	AGI	2	84.94			AGI	AGI	2	84.94
6	TTP	DGS	1	94.55
7	AGI	PQS	5	82.62
8	TOF	ABC	6	84.94
9
Match columns (3)

Cell Formulas
Range	Formula
G2:H5	G2	=FILTER(INDEX(A:B,SEQUENCE(ROWS(A:B)),{1,1}),ISNUMBER(MATCH(A:A,B:B,0)),"No Data")
I2:J5	I2	=XLOOKUP(INDEX($G2#,0,1),$B:$B,C:C)
Dynamic array formulas.

And, as a matter of interest, you mentioned that VLOOKUP was no good as it didn't expand for any extra rows but if used as below in I1 and J2 it will automatically expand/contract if columns G & H do.

aghaffar82.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			ABC	ABC	6	84.94
3	GHI	None	4	55.94			GHI	GHI	5	27.94
4	DMO	PPM	4	55.94			PQS	PQS	5	82.62
5	PQS	AGI	2	84.94			AGI	AGI	2	84.94
6	TTP	DGS	1	94.55
7	AGI	PQS	5	82.62
8	TOF	ABC	6	84.94
9
Match columns (4)

Cell Formulas
Range	Formula
G2:H5	G2	=FILTER(INDEX(A:B,SEQUENCE(ROWS(A:B)),{1,1}),ISNUMBER(MATCH(A:A,B:B,0)),"No Data")
I2:I5	I2	=VLOOKUP(INDEX(G2#,0,1),B:C,2,0)
J2:J5	J2	=VLOOKUP(INDEX(G2#,0,1),B:D,3,0)
Dynamic array formulas.

 

[aghaffar82]

Thank you so much Sir, works everything perfectly now. You have been very kind and patient with all these steps. Best Regards and a wonderful day ahead.