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,399
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,725
Members
415,853
Latest member
Newlife72

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