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

#### Endered5

##### New Member
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?

#### Attachments

• Test10.JPG
103.7 KB · Views: 11

#### jasonb75

##### Well-known Member
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Replies
4
Views
352
Replies
10
Views
586
Replies
6
Views
444
Replies
4
Views
111
Replies
8
Views
282

Threads
1,127,354
Messages
5,624,183
Members
416,017
Latest member
moritz210

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back