Forum Discussion
3 way lookup using index with match
We are working on a form that auto-populates info from a spreadsheet when info is entered. I need cell H3 to populate info based on info from cell B5 ('Product list'!B2:BZ2999,MATCH(BASF!B5,'Product list'!B2:B2999,0) then look at cell B7 ('Product list'!B2:BZ2999,MATCH(BASF!B7,'Product list'!E2:E2999,0) then populate info that matches for cell G3 - MATCH(BASF!G3,'Product list'!B1:BZ1,0)
My current formula is =IFERROR(INDEX('Product list'!B2:BZ2998,MATCH(BASF!B7,'Product list'!E2:E2998,0),MATCH(BASF!G3,'Product list'!B1:BZ1,0)),""). This works except where we have a Description (B7) that is the same in multiple places on the spreadsheet. That is why I need it to look at cell B5 1st, then B7, then populate the correct info to match G3. Is it possible to have a 3 way match with index/match or do I need to use something different? To say I am a newbie is an understatement! I appreciate any help you can give.
Please check the attached file.
11 Replies
Hi
It would be easier if you provide a sample Excel file to avoid random creation of the desired layout.
Ok, I attached the file. I highlighted in red the culprits that are causing my problems on the product lists tab. Currently it works... except it is pulling in the 1st GTIN# it comes to, not the one that goes with the Brand/Description in B5/B7
Hello,
ths will do the trick
=OFFSET('Product list'!$A$1;MATCH(1;INDEX((TRIM($B$7)=INDEX(TRIM('Product list'!E1:$E$35);;))*(UPPER(RIGHT($B$5;LEN($B$5)-3)&" "&LEFT($B$5;3))='Product list'!B1:$B$35);;);0)-1;MATCH($G$3;'Product list'!$A$1:$AE$1;0)-1;1;1)
