Multiple lookups of same value, possible?

Stu Dapples

Active Member
Joined
Jan 12, 2009
Messages
252
I have a lage spreadsheet (about 12000 records and counting!) I need to be able to search for a mould no. which may appear many times in the list (in one column). If I do a Vlookup, it will only return the first record.... Anyone got any tips on returning all iterations of the mould no. which I can then index off to the relevant info in adjoining columns?

I'm pretty sure I have done this before but its beating me this morning!

Any help appreciated ;)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Can't you just use AutoFilter or Advanced Filter?

I can but unfortunately the people I have to set the sheet up for can't be trusted not screw up the data! What I was hoping to do was hide all the main data and have a sheet they can type in the mould no. they are looking for which will then return all records relating to it....

Its hard work sometimes trying to account for the PC inferiority of mere mortals!! :LOL:
 
Upvote 0
Hi Stu,

I'm not an expert and I don't fully understand how this formula works, but it might do the trick for you if I understand you correctly ;)

If your mould numbers are in column A2:A12000 on sheet1 and the rest of the data is in columns B,C,D etc.

Then in A1 on Sheet 2 put this...
=COUNTIF(Sheet1!A2:A12000,A3)

In A3 on Sheet2 put the mould number you are looking for.

In A5 on Sheet2 put this copy down and across as you need...
=IF(ROWS(A$5:A5)<=$A$1,INDEX(Sheet1!A$2:A$12000,SMALL(IF(Sheet1!$A$2:$A$12000=$A$3,ROW(Sheet1!$A$2:$A$12000)-ROW(Sheet1!$A$2)+1),ROWS(A$5:A5))),"")

The above formula must be entered with ctrl-shift-enter NOT just enter.

I hope that works for you.

Ak
 
Upvote 0
Hi Stu,

I'm not an expert and I don't fully understand how this formula works, but it might do the trick for you if I understand you correctly ;)

If your mould numbers are in column A2:A12000 on sheet1 and the rest of the data is in columns B,C,D etc.

Then in A1 on Sheet 2 put this...
=COUNTIF(Sheet1!A2:A12000,A3)

In A3 on Sheet2 put the mould number you are looking for.

In A5 on Sheet2 put this copy down and across as you need...
=IF(ROWS(A$5:A5)<=$A$1,INDEX(Sheet1!A$2:A$12000,SMALL(IF(Sheet1!$A$2:$A$12000=$A$3,ROW(Sheet1!$A$2:$A$12000)-ROW(Sheet1!$A$2)+1),ROWS(A$5:A5))),"")

The above formula must be entered with ctrl-shift-enter NOT just enter.

I hope that works for you.

Ak

How can you say you are not an expert then pop a solution like that into the frame! Absolutley brilliant! Took a while to reconfigure it to the right sheets and ranges but its done the job a treat!

Only issue I have is that on certain mould numbers, it returns a #NUM error on the last row instead of the last record, not quite sure why but I think I can build an IF(ISERROR around it to get rid of it... Will have a play around and see what works!!

Many thanks for your help! (y)

Stu
 
Upvote 0
Hi Stu,

Trust me, I'm no expert, Andrew is, but I surely am not.
I just changed a formula that someone kindly did for me on here.
ake a look at ExcelIsFun for some great videos on this subject.

I don't even know how you get a #NUM error or what it means :ROFLMAO:

Good luck in resolving this.

Ak


Why can I not post sample data in a viewable way any more?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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