VLOOKUP question

G

Gamermatt

Board Regular
Joined
May 14, 2009
Messages
186
Hello,

I need a formula or macro to lookup text from column A in column B, and retrieve all matching cells' text from column C. It should be exported to columns D-I (will never have more than 6 matches). If there is no match, it should be left blank. If there is a match, the text from column C should be copied to column D, and if there is another match, the text should be copied to column E, F, etc.

Here is an example...

Before:
Excel Workbook
ABCD
1Dodge Charger Full Body KitsAcura CL Coiloversall*
2Dodge Charger Front BumpersAcura CL Front Bumpersall*
3Dodge Charger Rear BumpersAcura CL Front Bumpers1996-1999*
4Dodge Charger Side SkirtsAcura CL Front Bumpers2001-2003*
5Honda Civic Full Body KitsDodge Charger Rear Bumpersall*
6Honda Civic Front BumpersDodge Charger Rear Bumpers1996-1999*
7Honda Civic Rear BumpersDodge Charger Rear Bumpers2001-2003*
8Honda Civic Side SkirtsDodge Charger Full Body Kitsall*
Sheet1





After:
Excel Workbook
ABCDEF
10Dodge Charger Full Body KitsAcura CL Coiloversallall**
11Dodge Charger Front BumpersAcura CL Front Bumpersall***
12Dodge Charger Rear BumpersAcura CL Front Bumpers1996-1999all1996-19992001-2003
13Dodge Charger Side SkirtsAcura CL Front Bumpers2001-2003***
14Honda Civic Full Body KitsDodge Charger Rear Bumpersall***
15Honda Civic Front BumpersDodge Charger Rear Bumpers1996-1999***
16Honda Civic Rear BumpersDodge Charger Rear Bumpers2001-2003***
17Honda Civic Side SkirtsDodge Charger Full Body Kitsall***
Sheet1




Thanks,

Matt
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi Matt,

Try this Array-formula in D10 (Excel 2007 or higher)
=IFERROR(INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)),COLUMNS($D:D))),"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy across till I10 and down

HTH

M.
 
Last edited:
Upvote 0
Marcelo Branco said:
Hi Matt,

Try this Array-formula in D10 (Excel 2007 or higher)
=IFERROR(INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)),COLUMNS($D:D))),"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy across till I10 and down

HTH

M.
Click to expand...
Thanks for the response
I have excel 2003 and this does not seem to work, it says "#NAME?"
 
Upvote 0
Sorry,

Correct formula
=IFERROR(INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)-ROW($B$10)+1),COLUMNS($D:D))),"")
Ctrl+Shift+Enter

M.
 
Upvote 0
Marcelo Branco said:
Sorry,

Correct formula
=IFERROR(INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)-ROW($B$10)+1),COLUMNS($D:D))),"")
Ctrl+Shift+Enter

M.
Click to expand...

I used this updated formula, and changed the ranges according to what I need
=IFERROR(INDEX($C$1:$C$4362,SMALL(IF($B$1:$B$4362=$A1,ROW($B$1:$B$4362)-ROW($B$1)+1),COLUMNS($D:D))),"")

It still gives me "#NAME?" result
 
Upvote 0
Excel 2003 try this Array-Formula in D10

=IF(ISERROR(INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)-ROW(B10)+1),COLUMNS($D:D)))),"",INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)-ROW(B10)+1),COLUMNS($D:D))))

Ctrl+Shift+Enter

copy across till I10 and down

HTH

M.
 
Upvote 0
Marcelo Branco said:
Excel 2003 try this Array-Formula in D10

=IF(ISERROR(INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)-ROW(B10)+1),COLUMNS($D:D)))),"",INDEX($C$10:$C$17,SMALL(IF($B$10:$B$17=$A10,ROW($B$10:$B$17)-ROW(B10)+1),COLUMNS($D:D))))

Ctrl+Shift+Enter

copy across till I10 and down

HTH

M.
Click to expand...

That works. Thank you!
 
Upvote 0
Marcelo Branco said:
You are welcome.

Glad that is working :)

M.
Click to expand...

Actually, this formula did not work either.

For some products (example: if the product only had 1 cell to be copied, all), it worked. But for others, it did not.


Excel Workbook
ABCDEFGHIJ
1Dodge Charger Full Body KitsDodge Charger Accessoriesallall      
2Dodge Charger Front BumpersDodge Charger Coiloversallall      
3Dodge Charger Rear BumpersDodge Charger Fendersallall      
4Dodge Charger Side SkirtsDodge Charger Front Bumpersallall      
5Honda Civic Full Body KitsDodge Charger Full Body Kitsall1996-20002001-20032004-20052006-Upall1988-19911992-1995
6Honda Civic Front BumpersDodge Charger HoodsallallallIndividual WheelsWheel Packagesallall1988-1991
7Honda Civic Rear BumpersDodge Charger Lambo Doorsall1988-19911992-19951996-20002001-20032004-20052006-Upall
8Honda Civic Side SkirtsDodge Charger Rear Bumpersall1992-19951996-20002001-20032004-20052006-Upallall
9Nissan 350Z Full Body KitsDodge Charger Side Skirtsallallall1988-1991    
10Dodge Charger Trunksall
11Dodge Charger WheelsIndividual Wheels
12Dodge Charger WheelsWheel Packages
13Dodge Charger Wingsall
14Honda Civic Front Bumpersall
15Honda Civic Front Bumpers1988-1991
16Honda Civic Front Bumpers1992-1995
17Honda Civic Front Bumpers1996-2000
18Honda Civic Front Bumpers2001-2003
19Honda Civic Front Bumpers2004-2005
20Honda Civic Front Bumpers2006-Up
21Honda Civic Full Body Kitsall
22Honda Civic Full Body Kits1988-1991
23Honda Civic Full Body Kits1992-1995
24Honda Civic Full Body Kits1996-2000
25Honda Civic Full Body Kits2001-2003
26Honda Civic Full Body Kits2004-2005
27Honda Civic Full Body Kits2006-Up
28Honda Civic Rear Bumpersall
29Honda Civic Rear Bumpers1988-1991
30Honda Civic Rear Bumpers1992-1995
31Honda Civic Rear Bumpers1996-2000
32Honda Civic Rear Bumpers2001-2003
33Honda Civic Rear Bumpers2004-2005
34Honda Civic Rear Bumpers2006-Up
35Honda Civic Rods and Armsall
36Honda Civic Roofsall
37Honda Civic Side Skirtsall
38Honda Civic Side Skirts1988-1991
39Honda Civic Side Skirts1992-1995
40Honda Civic Side Skirts1996-2000
41Honda Civic Side Skirts2001-2003
42Honda Civic Side Skirts2004-2005
43Honda Civic Side Skirts2006-Up
44Nissan 350Z Full Body Kitsall
45Nissan 350Z Full Body Kits2000-2002
46Nissan 350Z Full Body Kits2003-2008
Sheet1


Nissan 350z does not work
 
Upvote 0
Actually, this formula did not work either.

For some products (example: if the product only had 1 cell to be copied, all), it worked. But for others, it did not.
Click to expand...

The results you showed in the post above are not ok?

What is wrong?

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

SaraWitch
  • Solved
Auto calculate based on multiple drop down options
Replies
7
Views
189
SaraWitch
SaraWitch
E
  • Question Question
Vlookup not working when range is extended.
Replies
1
Views
64
Fluff
Fluff
I
  • Question Question
VLookup question
Replies
2
Views
99
saboh12617
S
M
  • Question Question
Using text input box to call cell values.
2
Replies
12
Views
176
Skyybot
Skyybot
L
  • Question Question
Compile Error when Adding Attachments
Replies
5
Views
220
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

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