2

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:

enter image description here

enter image description here

I will be grateful to those who help

Rafael

Improve this question
1
  • 2
    Change R2C1:R21C7 to C1:C7 or if you don't want to lookup an entire column (may be slow) change to something very large that you know data will not go beyond (e.g. - R2C1:R5000C7 Commented Sep 21, 2017 at 16:03

1 Answer 1

Reset to default
5

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)"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.