Inverse LOOKUP in google sheets to return column name

0
votes

Good day, I am currently sorting a storage unit, where various parts from samples are stored in multiple locations. The idea is to sort it. So I am creating a spreadsheet for each part. The columns will be the location and the rows are a list of the sample numbers of which the parts can be found in this location.

input:

input

A spreadsheet like this will exist for every part.

The idea is to have a final table, sorted by sample number, which has the parts in the columns. I want the cells to return where this part of this sample is stored:

desired output:

desired output

I tried various LOOKUP formulas but they do not return the column name. Because this has to be accessible by multiple people, it has to be in google sheets. This is an example file: https://docs.google.com/spreadsheets/d/1pUmTs0mLoZAdPc83pLXC75MCUF2P1SHDtEfYPEMohr4/edit?usp=sharing I am super thankful for any help!

1
google-sheets
share a copy of your sheet – player0
docs.google.com/spreadsheets/d/… This is obviously not the data, but an example that should clarify what I mean. Thank you already @player0 – Julian
Would you be okay with an Apps Script solution? If not then it seems every cell or at least every row should contain a formula as instead of having one formula inserting the full range. – Raserhin
I am unfamiliar with Apps Script, so ideally the solution is without it. But if that is the only solution I will need to learn about it. The parts are from over 10 000 samples, so changing the formula for every row is not really an option. – Julian

1 Answers

0
votes

With the help of this website: https://infoinspired.com/google-docs/spreadsheet/search-across-columns-and-return-the-header/ I found a solution. The idea is to use match functions for each column. For the example posted the code looks like this:

=ifs(isna(match(A2,'Part 1'!$A$1:$A$7,0))=FALSE,'Part 1'!$A$1,isna(match(A2,'Part 1'!$B$1:$B$7,0))=FALSE,'Part 1'!$B$1,isna(match(A2,'Part 1'!$C$1:$C$7,0))=FALSE,'Part 1'!$C$1,isna(match(A2,'Part 1'!$D$1:$D$7,0))=FALSE,'Part 1'!$D$1)

I know it is not pretty, but it works, as the number of columns is limited. The website also suggests a dynamic solution with a query function, but that only works when the cell left of the cell of interest has entries.

Thanks to everyone :-)