Formula to lift multiple information from rows and place it in a separate worksheet

Hello!

Myself and some colleagues are currently working on amassing a large amount of data which we currently have in a written format and are putting them into Excel Spreadsheets for the purpose of centralising them within a software system we are configuring.

To give you an idea of what we are doing: EXAMPLE.xlsx

In a separate sheet within the workbook, we need a formula that will look up the relevant parts of the table depending on what information is typed into a certain cell.

For example (and referring back to the sample attached), if I were to type 9/16 - 20 UN 2A into a cell, I would need it to lookup cells D1 - K1 and populate them into certain cells.

Is there a way to do this relatively simply? The entire table consists of over 1000 rows.

I hope I have explained this relatively clearly, it is not my area of expertise. I would appreciate any help you can offer me and thank you for it in advance.

Merged cells is usually a bad thing but it seems to work here regardless.

Hi,

I notice from the attachment that your desired results do not seem to include this latest request, only returns for REF1-8 (which Jacc supplied).

Perhaps in order to further clarify your desired return(s) you could re-post your attachment with a clear outline of the results you would wish to see?

Regards

I have tried by removing the merge.

Please have a look.

Hi,

I have to say that the way you've set up your data table, not only with merged cells but also with apparent inconsistencies in layout, makes any solution extremely complex.

Can you just confirm that the layout of your two tables is correct? It would seem a little inconsistent that, in the External Threads section, the Max data precedes that for the Min, whereas it is the reverse in the Internal Threads section.

Regards

Hi Garion,

The updated function will work for A & B

Rgds,