Multiple lookups of same value, possible?

S

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 ;)
 
Sort by date Sort by votes
Andrew Poulsom said:
Can't you just use AutoFilter or Advanced Filter?
Click to expand...

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!! :laugh:
 
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
Akashwani said:
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
Click to expand...

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! :bow:

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 :rofl:

Good luck in resolving this.

Ak


Why can I not post sample data in a viewable way any more?
 
Upvote 0
You must log in or register to reply here.

Similar threads

megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
539
etaf
etaf
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
380
BenW71
B
B
  • Question Question
Use INDIRECT combined with INDEX MATCH (multiple row criteria) to return values from specified sheet
Replies
4
Views
747
Peter_SSs
Peter_SSs
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
813
Peter_SSs
Peter_SSs
O
  • Solved
I want it all! Using single lookup command to return both multiple Rows and Columns
Replies
4
Views
632
Fluff
Fluff

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

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