I used an Index Match formula in Excel VBA to find part number and description for tools out of database. It was an Index-Match formula with to condintions. first look up a match in certain array for tool type of a machine and than match it to a configoration from certain array.
My problem is that I want to change to lookup way because with the formula I write specific range and I want it to be flexable so that if I add a new row in the database I would not need to change the code. the the databse is a table name "Illuminators".
That was the formula with to conditions I used before and as you can see there is a cell refernce and if i would add a new tool to the databse table under the array, it would not look for it there.
Selection.FormulaArray = "=INDEX(DB!R2C1:R21C7,MATCH(1,(DB_Illumiators!R2C1:R21C1=RC[1])*(DB_Illumiators!R2C2:R21C2=RC[2]),0),4)"
I use the formula in the main sheet in a loop and I use it from the databse work sheet in the second picture:
I will be grateful to those who help
Rafael
Change R21C7
, R21C1
and R21C2
to INDEX(DB!C7, MATCH("zzz", DB!C1))
, INDEX(DB!C1, MATCH("zzz", DB!C1))
and INDEX(DB!C2, MATCH("zzz", DB!C1))
. These will dynamically adjust the terminating cell depending on the last text val;ue in DB!A:A.
Selection.FormulaArray = _
"=INDEX(DB!R2C1:INDEX(DB!C7, MATCH(""zzz"", DB!C1)), MATCH(1, (DB_Illumiators!R2C1:INDEX(DB!C1, MATCH(""zzz"", DB!C1))=RC[1])*(DB_Illumiators!R2C2:INDEX(DB!C2, MATCH(""zzz"", DB!C1))=RC[2]), 0), 4)"