CODEWITHSUNDEEP
excelindexingexcel-formulamatch
Sarguroh Wahid
Sarguroh Wahid

Reputation: 15

Index Match with multiple references

enter image description here

Item ID Description Fifteen Digit   Fourteen Digit  Thirteen Digit  Twelve Digit    Eleven Digit    Ten Digit   Nine Digit  Eight Digit Seven Digit Six Digit       Item ID
1100001 POS - BANK MACHINE  -   -   -   -   -   1100001123  110000112   11000011    1100001 -   ABC 1100001
1100280 ALIEN EDP 30 ML (30x1)  -   -   -   -   -   1100280123  110028012   11002801    1100280 -   ABC 1100280
1100288 IN LOVE WITH DIOR COFFRET   -   11002881100288  -   -   -   -   -   -   1100288 -   ABC 1100288
1100297 LACOSTE INSPIRATION 75ML    -   11002971100297  -   -   -   -   -   -   1100297 -   ABC 1100297



                Barcode Item ID Description                         
                1100280123

Hi I want to look up the Item ID, there are multiple barcodes for one item ID. If I put any barcode than its should provide its respective Item ID.

Please help me out on this. I tried index match with LEN (to count no. of digits in a barcode), but it doesnt work.

Upvotes: 0

Views: 557

Answers (2)

Mrig
Mrig

Reputation: 11712

Enter the following following formula in Cell F10

=INDEX(A$2:A$5,MIN(IF($E10=$C$2:$L$5,ROW($C$2:$L$5)-1)))

This is an array formula so commit it by pressing Ctrl+Shift+Enter. Drag/Copy to Cell G10 and if required drag/copy down. See image for reference.

enter image description here

Upvotes: 0

Domenic
Domenic

Reputation: 8124

Let's assume that A1:N5 contains the data, and that H10 contains the bar code of interest, enter the following formula in I10, confirm with CONTROL+SHIFT+ENTER, and copy to J10...

=INDEX(A$1:A$5,SMALL(IF($C$1:$L$5=$H10,ROW($C$1:$L$5)-ROW($C$1)+1),1))

Note that the formula can be copied down the columns, if necessary.

Hope this helps!

Upvotes: 1

Related Questions