Some type of LOOKUP Function

Ok i am wanting to do something similar to a VLOOKUP that isn't limited.

These are the 2 sheets i am using:
excelforum1.JPG

excelforum2.JPG

on the first picture Bill of Materials Tab in cell D2 i am wanting it to run a check to see if Cell A2 can be found on image 2 =Sheet1!C2:R33000 If it is found i want it to i want it to pull the <ID> from column A on the same row the ID was found on the second image (Sheet1).

Hope that makes sense and would that be possible?

Thanks

Jack

For some reason the file is failing to upload. I am guessing it is because it exceeds the file size limit.

The forum rule that davsth is referring to is the rule about post titles. Your title MUST reflect your issue, not a question like how can this be done or problem or formula or help needed.
If a moderator comes along the post will be locked until you change to an appropriate title. Think in terms of someone else with a similar problem to yours, what search terms would they use to find this post?

And technically nobody is supposed to offer help until you change the post title when that has been requested by a senior forum member.

As for attachments, you don't need to provide the whole data set, just a representative sample with enough samples to show what you are dealing with AND your expected results.

Thanks.

Thanks i finally found out how to edit it. The reason i didnt name it in the first place is because i dont know what the function is called that i need to use.

The file size limit for an .xlsx is about 1Mb. however, it's about 9 for an .xlsb

if you DO decide to post the whole thing, don't forget to tell us where to look. It's very annoying to be given a file with 10 worksheets and no indication of where the problem is. Normally, I simply close the file and look at anther thread.

Untested (60000+ rows does not encourage me to do any manual checking of results...).

=IFERROR(INDEX(Sheet1!$A$2:$A$65000,SUM(INDEX((Sheet1!$C$2:$R$65000=A2)*ROW(Sheet1!$C$2:$R$65000),))-ROW(Sheet1!$C$2)+1),"")

copied down.

This is closer, but not there yet. You can see there are a few #REF errors. These can be caused if there are duplicates. Certainly the first 3 #REFs are duplicates. Since your "sample" was 60,000 rows i didn't check to see if any of the answers that "worked" were also duplicated. You can check a few!! Since there are duplicates, which column A value do you want to be returned: the first or the last?

Update: I'm pretty sure that it's the duplicates that are the problem. So, to repeat: how do you want that handled: first, last, or what??

One tweak to the formula, as well.

Until you are completely OK with this, I have reduced the massive sheet to a manageable sample. See the sheet. This is easier to test!!

Please try to identify faults with it. It allows for duplicates. Can there ever be triplicates... or more? If so, how many is the maximum?

I think we may be talking at cross-purposes. At least, i hope we are!!
I don't mind how many values occur twice. It can be 1, or 10,0000. Can values occur more than twice? If so what is the maximumn number of times that a value can occur?

if it's 12, we'll need a diffferent approach altogether.

oh i just found one with more i think this is what you meant when you said duplicates. To make it more clear what we are trying to achieve i will post a brief summary. We are currently using a database system that holds all of our QA documentation but next year in February we are moving to a new system and we need to extract all of the current documentation out of the current system along with a spreadsheet which shows what product each QA Doucment is linked to. Unfortunately our system doesn't have the functionality to allow us to export the Product ID's the document is linked to so i had to export as much information as i could and now i am trying to match them up against the product codes. It is proving rather difficult though as you have noticed some document ID's are linked to multiple products.

excelforum3.JPG

OK. We have reached the end of the road. This will not be possible with a formula. You have three choices.

1. Put each product code in a separate column. It will be slow to run, but can be done.

2. Use VBA, in which caese, I'm out... as I am no good with VBA.

3. Use a UDF that I often use... However, I strongly suspect it'll fall over with such a large dataset.

Only you can close the thread. If you do go down the VBA route, I suggest that you close this one (if for no other reason that many folk don't look at threads with loads of replies) and re-start in the VBA sub-forum, CLEARLY stating the issue with multiple replicates... right from the start.

Me again. Inspiration struck when I was doing something else (idiotic really - it should have occurred to me immediately) Two formulae. One to return the first product code. A second to return all of the rest. By starting the second formula with "If the previous column is blank, do nthing, otherwise run the lengthy calculation" the processing time was about 30 seconds. Try it.

In D2, copied down:
=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$C$2:$R$65000)/(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$R$65000))),COLUMNS($D:D))),"")

in E2, copied across 35 columns and then down:
=IF(D2="","",IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$C$2:$R$65000)/(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$R$65000))),COLUMNS($D:E))),""))

Do let me know if it worked out. If it didn't... shout!!

And, of course, you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.