Multiple criteria Vlookup by using INDEX, MATCH and Indirect (error).

[Endered5]

Hi

I have a problem here that i cant solve. I think it's the syntax. What I want to do is making a Vlookup but looking at 2 criterias (column B and I) and taking the comments from column K. I think it will be easier to understand if you look at the screenshot.


The formula I tried:
ActiveCell.FormulaR1C1 = "=INDEX(Indirect(r1c19):Indirect(r2c19),MATCH(RC[-8]&[RC-1],Indirect(r1c17):Indirect(r2c17)&Indirect(r1c18):Indirect(r2c18),0),1)"

Why do I get an error here and what would the solution be?

 

[jasonb75]

However, the code seems a bit tricky and complex.

That is what I meant when I said that it is not a solution.

A solution would be much simpler and easier to understand, but this was the best I could do with the way that your sheet is arranged.

All the code does is create the formula on the fly by concatenating bits of text with values from vba before passing it to the sheet.
& Range("S1").Value & is doing the same as INDIRECT("S1") would have done in your earlier attempt. This works with the references in the top right corner of your example as they are in a fixed position. For the parts where the formula needs to look at the same row that it is located in, ActiveCell.Row is used to get the row number based on the active (selected) cell.