08-30-2019 07:59 AM
08-30-2019 07:59 AM
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.
09-03-2019 07:17 AM
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
09-04-2019 03:28 AM
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)
09-16-2019 02:58 PM
Guess I replied too fast. The spreadsheet has 3 areas for drop downs to come up with the GTIN and Item#. The formula you gave appears to work for most HarvXtra (D1 drop down), but it is not working when you choose hvxPRIVATE.LABEL (B5), and variety drop down(B7), or for and Conventional or Roundup.Ready (D1), with any (B5) and (B7) options. It is returning #N/A. The formula you gave is way over my head to be able to figure out where the problem is. I suspect maybe it is too specific somewhere... I attached the spreadsheet with your formulas in for GTIN and ITEM#. I expanded the 'search' parameter from what you had given. The product list tab that is attached, is not the full product list
09-17-2019 09:42 AM
the problem is that in the table you have hvxAMERICAS while on the first page you have AMERICAS HVX. What I have done her is to get 3 letters from left of hvxAMERICAS to have "hvx" , then getting rest of the text which is AMERICAS and merging them to form AMERICAShvx, converting this to all caps which results in AMERICASHVX. Than I search this text in the ProductList page B column with removing spaces of all the values.
So what has become here is I get 3 letters from the left of the text. with this option you will have VAMERICASCON and this text can not be found on B column of Product list.
09-17-2019 03:44 PM
ok - see attached. I cleaned up the report. C5 doesn't start with the trait, it now starts with the brand for each of the Conventional, Roundup.Ready, and Harvxtra and matches Product list column B. Don't think you need trim and len now?
I need to be able to have the GTIN and ITEM# populate with all options, not just HarvXtra and that one variety that is duplicated for different Brand/Customer . So, cells D1, B5, and B7 determine the GTIN & ITEM#.
My original formula =IFERROR(INDEX('Product list'!B2:BZ2998,MATCH(BASF!B7,'Product list'!E2:E2998,0),MATCH(BASF!G3,'Product list'!B1:BZ1,0)),"") worked, except for the items that had the same Variety name. So I need the formula to also look at B5 (Brand/Customer) to come up with the correct GTIN & ITEM#.
Hopefully this makes it more straight forward, and less complicated?
09-17-2019 11:01 PM
int the previous post I attached a file to solve the issue. However with you new setup you can use the attached file.