Search results Multiple Rows

P

poikl

Active Member
Joined
Jun 8, 2002
Messages
466
Platform
  1. Windows
Hi, I'd really appreciate if you can please help me construct the formula needed to bring multiple results from many rows.
(I know that a regular VLOOKUP will only bring the first result but here it's possible that there will be up to 6 required results).
I received a file "Current", which lists item #'s in ColA and supplier in ColC. I have a Data Base "Parts" which lists all 30,000 parts. The Id# is sorted and listed in ColB and the approved supplier in ColF in the "Parts" file. Now I'd appreciate if you please construct a formula in Columns F thru K of the "current" file which looks up the Id# in ColA and then searches in ColB of the "Parts" file and brings the approved supplier listed in ColF of the "parts" file. Thank you in advance
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Assuming your data

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>"Parts" File - Sheet1</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:32.32px;" /><col style="width:76.04px;" /><col style="width:26.61px;" /><col style="width:26.61px;" /><col style="width:26.61px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >Parts</td><td > </td><td > </td><td > </td><td >Supplier</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td >prov8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">456</td><td > </td><td > </td><td > </td><td >sup1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">456</td><td > </td><td > </td><td > </td><td >sup2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="text-align:right; ">456</td><td > </td><td > </td><td > </td><td >sup3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="text-align:right; ">456</td><td > </td><td > </td><td > </td><td >sup4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td style="text-align:right; ">789</td><td > </td><td > </td><td > </td><td >a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="text-align:right; ">789</td><td > </td><td > </td><td > </td><td >b</td></tr></table>


Array formula on "current" file:

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>"Current" File - "Current" Sheet</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:24.71px;" /><col style="width:76.04px;" /><col style="width:14.26px;" /><col style="width:14.26px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >ITEM</td><td > </td><td >SUPPLIER</td><td > </td><td > </td><td >SUPPLIER1</td><td >SUPPLIER2</td><td >SUPPLIER3</td><td >SUPPLIER4</td><td >SUPPLIER5</td><td >SUPPLIER6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">123</td><td > </td><td > </td><td > </td><td > </td><td >prov1</td><td >prov2</td><td >prov3</td><td >prov4</td><td >prov5</td><td >prov6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">456</td><td > </td><td > </td><td > </td><td > </td><td >sup1</td><td >sup2</td><td >sup3</td><td >sup4</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >{=IFERROR(INDEX([Parts.xlsx]Sheet1!$F$1:$F$20,SUMPRODUCT(SMALL(IF([Parts.xlsx]Sheet1!$B$1:$B$20=$A2,ROW($B$1:$B$20)),COLUMNS($F$1:F$1)))),"")}</td></tr></table></td></tr></table>


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Then copy the formula to the right up to the K column and then copy it down.
 
Upvote 0
Can you post some sample data that we can work with?
 
Upvote 0
Thank you for helping me. (didn't have a chance to test till now
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Search Boxes for users
Replies
2
Views
321
Eurekaonide
E
M
  • Question
Creating Results Table + Hiding columns based on text in specific row
Replies
4
Views
553
MelissaBr
M
M
  • Question
Creation of Results Table + Hiding of Sheets and Columns
Replies
1
Views
383
MelissaBr
M
K
  • Solved
VBA - Sending a single email with all entries to multiple recipents
Replies
4
Views
475
LMacD
L
F
  • Solved
Webscraping a site with no element ids and returning data
Replies
6
Views
1K
fizzydrink
F

Forum statistics

Threads
1,214,833
Messages
6,121,869
Members
449,054
Latest member
juliecooper255

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