CODEWITHSUNDEEP
excel-formula
KROLN
KROLN

Reputation: 300

Index search in multiple columns with row 1 as result

I was trying to modify that formula below. I am sure it is obvoius how to modify it, but now I need fresh approach. I spend to much time with it.

{=INDEX(WholeTable,MATCH(1,MMULT(--(ColumntSearch=[@Barcode]),TRANSPOSE(COLUMN(ColumntSearch)^0)),0),1)}

How it works. I have list with scanned barcodes and the second one with informations to those barcodes. Sadly few barcodes belong to the same row and are documented in one of 29 barcode columns.

That formula above is searching for barcode in different columns (29 of them,ColumnSearch) and giving as result one specific column in found row with searched barcode. In that example, it is the first one.

Example: Rw04235 was found in column K; row 2345. I get as result value from first column of row 2345. ex. ID2432 ...................

I need it to be modifed to find in which column of 29 is my barcode and give me only result from that column first cell.

Upvotes: 0

Views: 36

Answers (1)

KROLN
KROLN

Reputation: 300

=COLUMN(INDEX(WholeTable,SUMPRODUCT(ROW(ColumntSearch)*(WholeTable=[@Barcode])),SUMPRODUCT(COLUMN('Source for S. Scanning'!$A$4:$AH$4)*(WholeTable=[@Barcode]))))

It gives me back which column contain that barcode, so I can use that column number to get first row value thanks!

EDIT (from OverflowStacker)

=SUMPRODUCT(ROW(ColumntSearch)*(WholeTable=[@Barcode]))

gives the row-number of the match.

=INDEX(WholeTable,SUMPRODUCT(ROW(ColumntSearch)*(WholeTable=[@Barcode])),1)

should suffice to get the wanted value. Adapt the 1 according to your needs.

Upvotes: 1

Related Questions