Index & Match Look-Up

Hi There...

I am trying to solve a lookup of a particular value but the database has more than one row of which the lookup value is the same. On the attached workbook the commodity code is entered in the yellow cell. The blue cells then needs to return the relevant values (which I have typed in manually to show the required answer) by looking them up on the two relevant SQL Tables called "InvMaster" & "BomStructure" and in one case it also needs to change the letter "S" to the word "Bay" in front of the numerical number i.e. S103 must be Bay 103.

Any help would be appreciated as I am not getting the Index & Match to work correctly.

Thank You
Colin

Originally Posted by JohnTopley

I think you need to add more examples to avoid us making assumptions: how the we know which components are "cores" unless they always start with "32"?

Hi John,

All the data works in the same manner whereby the "cores" will always start with "32-" and ends with a "-1" etc depending how many different types of cores. (Could be up to 7 types) The only thing differentiating them from each other is the numerical number in the middle that will always be the same as the commodity code typed in the yellow cell i.e. 31-20050033-1; 31-20050033-2; 31-20050033-3 etc. up to -7 if there are 7 types of cores.

Hi John,

Thank you for the help thus far. I see the formulae for C2 is working perfectly. Thank you! However the formulas in the other cells from C3-C11 do not change the value when the commodity code in B1 changes. I have added a second item into the database for reference. If the commodity code is changed from 20050033 to 20090078 the work centre (in cell C2) changes correctly. But the Core numbers do not change as there is no reference to the cell B2. All the changes on the cells need to always refer to the entry done in B2. See attached workbook with an extra set of data loaded.

Originally Posted by JohnTopley

Must have had a senior moment ...

in C3

=IFERROR(INDEX(BomStructure!$F$2:$F$1048576,SMALL(IF((BomStructure!$A$2:$A$1048576=Sheet1!$B$1)*(LEFT(BomStructure!$F$2:$F$1048576,2)="32"),ROW(BomStructure!$F$2:$F$1048576)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")

in C10

=IFERROR(INDEX(BomStructure!$F$2:$F$21,SMALL(IF((BomStructure!$A$2:$A$21=B1)*(LEFT(BomStructure!$F$2:$F$21,1)="4"),ROW(BomStructure!$F$2:$F$21)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")

in C11

=IFERROR(INDEX(BomStructure!$F$2:$F$21,SMALL(IF((BomStructure!$A$2:$A$21=B1)*(LEFT(BomStructure!$F$2:$F$21,1)="7"),ROW(BomStructure!$F$2:$F$21)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")

And range maximum to something sensible as I assume you don't have 1 million + rows.

Hi John,

Although this thread have been marked as solved there is more problems that I have to overcome which I did not originally foresee. Do I need to post a new thread for below or can we continue on this one? Due to the complexity of the lookups I am trying to achive I knew that you would know how to do it.

On the attached workbook I am trying to get the formula to work for the following cells on sheet1:
Cell C12 will need to look for a value that will always have a "D-" in front of it based on the entry in cell B1.
Cell C13 will need to look for a value that will always have a "32-3" in front of it. (Core Irons will always have a "32-3" in front of it. Cores that you solved in the lookup for cells C3 to C9 always has a "32-2" in front of it.)

Then I also need to return the qty for each of the items (cells C3 to C13) based on the original commodity code entered into cell B1. The value of the qty is located in database called "BommStructure" in column M called "QtyPer".

There is no data for "Ceramic Inserts"; and as the "Core Irons" are not related to the "Parent" then, if there is more than one "set" of data in "Bom Structure", there is no way of relating the "Core Iron" to the correct parent. Searching on "D" will always find the first in any list.

in C12

=IFERROR(INDEX(BomStructure!$F$2:$F$31,SMALL(IF((LEFT(BomStructure!$F$2:$F$31,1)="D"),ROW(BomStructure!$F$2:$F$31)-ROW($G$2)+1,""),ROWS($G$2:G2))),"")

in C13

=IFERROR(INDEX(BomStructure!$F$2:$F$31,SMALL(IF((LEFT(BomStructure!$F$2:$F$31,4)="32-3"),ROW(BomStructure!$F$2:$F$31)-ROW($G$2)+1,""),ROWS($G$2:G2))),"")


in D3

=INDEX(BomStructure!$M$2:$M$31,MATCH($B$1&$C3,BomStructure!$A$2:$A$31&BomStructure!$F$2:$F$31,0))

ALL entered with Ctrl+Shift+Enter