Three Columns to concatenate on a vlookup...

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

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

confirmed with Ctrl + shift + enter.
Book1
BCDEFGHIJ
1
2aaafffccc1
3aaabbbccc3aaafffccc2
4aaabbbccc3
5aaahhhccc4
6aaahhhccc5
7aaahhhddd6
8aaahhhddd7
9aaahhhddd8
Sheet4
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Works for me.

Setup sheet, with a concatenated column as the left-most column ...
Book26
ABCDE
1Conc.Col1Col2Col3Col4
2Alpha1aAlpha1aa1
3Alpha2bAlpha2ba2
4Alpha2cAlpha2ca3
5Alpha2aAlpha2aa4
6Alpha1bAlpha1ba5
7Alpha2dAlpha2da6
Sheet1


The summary sheet pulling the information, notice not extra column - the concatenated value resides inside the formula ...
Book26
ABCD
1Col1Col2Col3Col4
2Alpha1ba1
Sheet2



HTH
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
I'm pretty sure this is exactly what you did, but here's what I came up with:
Book1
ABCDEFGHI
1MatchCriteria1Criteria2Criteria3ConcatColAColBColCItem#
2Item2A2B8C2A1B9C1A1B9C1Item1
3A2B8C2A2B8C2Item2
4A3B7C3A3B7C3Item3
5A4B6C4A4B6C4Item4
6A5B5C5A5B5C5Item5
7A6B4C6A6B4C6Item6
8A7B3C7A7B3C7Item7
9A8B2C8A8B2C8Item8
10A9B1C9A9B1C9Item9
Sheet1


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

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

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
Top