Lookup Row Number based on known Column Number

[Adb123]

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.

[shank_mis]

Let's assume your data is in column A to Column H and Row 2 to Row 17.
Row 1 is fixed for header.

Place your data validation in J1 & J2.
J1 for Column number C1:H1
J2 for Row Number B2:B17

Now in Cell J3: ="Column "&MATCH(J1,A1:H1,0)&" and Row "&MATCH(J2,B1:B17,0)
And...you are done.

[Pete_UK]

It would be better if you attached a sample Excel workbook, rather than a picture of one. That way we would be able to see which columns you are using for your data and cells where you want to specify the colour and value. See the yellow banner at the top of the screen for details of how to do this.

Hope this helps.

Pete

[Adb123]

Hi Pete,

Thanks. I've now updated the original post with the example spreadsheet and a little bit more information on what I'm looking for.

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[Pete_UK]

You can put this formula in cell M6:

=IFERROR(INDEX($B$3:$B$18,MATCH(ROWS($1:1),INDEX($A$3:$H$18,0,$M$5),0)),"")

and then copy it down. If you want to pick up the final two items on your list, then for completeness you should fill cells C17:H17 with 5 and C18:H18 with 6 (as on your screen shot), or use a different formula for those two cells (could be =B17 in cell M10).

Hope this helps.

Pete

[Adb123]

Hi Pete,

That worked perfectly. Thank you. And yes, I for some reason didn't do 5 and 6 on the spreadsheet. I'm unsure why.

Again, thank you for this. I'll be able to complete my spreadsheet now.

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete