Hi all,
I'm in a really tricky spot with a document I'm doing. I work for an engineering company and I'm looking at automating some data pulls from Bills of Materials. I can make it do absolutely everything I need it to, bar find the row number I need.
I can find the given column number using MATCH.
I am then looking for a row by searching down that column, and telling me which row the Number 1 appears in.
Example.PNG
For example. If I picked "Red". It would tell me Column 3 (which I can find), and then because it's looking for Item Number 1, it would throw back "Row 2". This is because it would search for the Number 1 down Column 3. If I was looking for Green, it would tell me Column 5 and Row 4. If I was looking for Item 4 for Yellow, it would tell me Column 6 and Row 13. I need this to be automated based on which Colour you pick (I'd use data validation etc. to give us a drop down, and from that it would populate a pick list for everything we need)
As I said, finding the column is the easy part. If I can find the row I can then use Index to let me find the part names.
Is this something that can be done easily? I've been struggling with this for a while. Seems like it should be easy. I can do it with a large "IF" function, but would rather it be much simpler than that.
Thanks.
EDIT: Added example Spreadsheet. In this Spreadsheet I've wrote in the Text next to Item 1, 2, 3, 4, 5 and 6 that I would like it to automatically find by using the drop down. I think the way I've got the data presented might make this doable. If I need to change the way the data is presented let me know too and I can change that probably.
Bookmarks