Forum Discussion
3 way lookup using index with match
Please check the attached file.
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.
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?
Thank you. In the previous message I didn't see any change in the formula and it was not working in your attachment. This one appears to be working fine now. Appreciate your help!
Hello,
int the previous post I attached a file to solve the issue. However with you new setup you can use the attached file.