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

Endered5

New Member
Joined
Oct 3, 2020
Messages
25
Office Version
  1. 2016
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
    Test10.JPG
    103.7 KB · Views: 11

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top