Three Columns to concatenate on a vlookup...

[FinancialAnalystKid]

I have three columns in a spreadsheet and need to match these three columns to three columns in another sheet. If it matches, I want to retrieve the value in the a 4th column. Is this possible?

I was concatenating the three columns on both sheets in order to use vlookup but would like to do this without a macro and without adding more columns to the sheet.

I can't figure it out.

 

[tactps]

You could use a sumproduct formula or perhaps index and match formulas.

If you post your worksheet, it will be easier for us to see what you are trying to do.

 

[fairwinds]

Hi,

Try:

=INDEX(J2:J9,MATCH(B3&"@"&C3&"@"&D3,G2:G9&"@"&H2:H9&"@"&I2:I9,0))

confirmed with Ctrl + shift + enter.

Book1
	B	C	D	E	F	G	H	I	J
1
2						aaa	fff	ccc	1
3	aaa	bbb	ccc	3		aaa	fff	ccc	2
4						aaa	bbb	ccc	3
5						aaa	hhh	ccc	4
6						aaa	hhh	ccc	5
7						aaa	hhh	ddd	6
8						aaa	hhh	ddd	7
9						aaa	hhh	ddd	8
Sheet4

 

[Zack Barresse]

Works for me.

Setup sheet, with a concatenated column as the left-most column ...

Book26
	A	B	C	D	E
1	Conc.	Col1	Col2	Col3	Col4
2	Alpha1a	Alpha	1	a	a1
3	Alpha2b	Alpha	2	b	a2
4	Alpha2c	Alpha	2	c	a3
5	Alpha2a	Alpha	2	a	a4
6	Alpha1b	Alpha	1	b	a5
7	Alpha2d	Alpha	2	d	a6
Sheet1

The summary sheet pulling the information, notice not extra column - the concatenated value resides inside the formula ...

Book26
	A	B	C	D
1	Col1	Col2	Col3	Col4
2	Alpha	1	b	a1
Sheet2

HTH

 

[PA HS Teacher]

I'm pretty sure this is exactly what you did, but here's what I came up with:

Book1
	A	B	C	D	E	F	G	H	I
1	Match	Criteria1	Criteria2	Criteria3	Concat	ColA	ColB	ColC	Item#
2	Item2	A2	B8	C2	A1B9C1	A1	B9	C1	Item1
3					A2B8C2	A2	B8	C2	Item2
4					A3B7C3	A3	B7	C3	Item3
5					A4B6C4	A4	B6	C4	Item4
6					A5B5C5	A5	B5	C5	Item5
7					A6B4C6	A6	B4	C6	Item6
8					A7B3C7	A7	B3	C7	Item7
9					A8B2C8	A8	B2	C8	Item8
10					A9B1C9	A9	B1	C9	Item9
Sheet1

I look forward to seeing how this can be done with some sort of array formula.