# 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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### jasonb75

##### Well-known Member
The reference to I24 in the match range is incorrect, it should be RC[-1] and not [RC-1] as you have used. Correcting it will still not work as it needs to be array confirmed which can not be done when using R1C1 references in vba.

There are a number of ways to make it work, however none of them are really solutions. Given that there are other possible flaws in what you're attempting, it might be a better idea to rethink the process.

The formula is not case sensitive so Red and red are both matches. Given that there are 4 rows that meet the criteria, what result do you expect?
What is the purpose of having the index and match ranges in Q1:S2 instead of entering the range directly into the formula?

#### Endered5

##### New Member
Because the table range I want to find can vary in terms of rows. Q1:S2 is another process that I get in order to get the range of the table that I want to lookup.

#### jasonb75

##### Well-known Member
Please post that process and answer the other question in my previous post.

#### Endered5

##### New Member

ADVERTISEMENT

Please post that process and answer the other question in my previous post.
Hi ok.

So this is a bigger problem. I have several tables, called A1-D1, A2-D2, A3-D3 and so on. What i want to do is a Vlookup that compares the A2 table with A1 table, B2 table with B1 table and so on.

For example in the picture, I'm in "J24", which belongs to table A2, and I want the lookupvalue for "comment2" in table A1. In this case it's multiple lookup value "header B" and "header X" that I want to find. So i want to find E & X in "Table A1.

My whole formula look like this:

Excel Formula:
``````Sub Vlookup()

Dim M As Range
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Range
Dim N As Range
Dim rowcount9 As Integer

ActiveCell.Offset(-2, -9).Select
ActiveCell.Copy
Range("M1").PasteSpecial

Range("M1") = Range("M1").Value
Range("M2") = "=Match(M1-4,A:A,0)"
Range("M3") = "=Match(M1-3,A:A,0)"
Range("M4") = "=Match(M1-0,A:A,0)"
Range("M5") = "=Match(M1+1,A:A,0)"

rowcount9 = Cells(Rows.Count, 2).End(xlUp).Row
Range("M4").Copy

Range("M10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("M10") = Range("M10").Value
Range("M2") = Range("M2").Value
Range("M3") = Range("M3").Value
Range("M4") = Range("M4").Value
Range("M5") = Range("M5").Value
Range("O2") = "B"
Range("O3") = "K"
Range("O4") = "I"

M4 = Range("M4").Value
M5 = Range("M5").Value

Range("P1").Value = Range("O2") & Range("M2")

Range("P2").Value = Range("O3") & Range("M3")

Range("Q1").Value = Range("O2") & Range("M2")
Range("Q2").Value = Range("O2") & Range("M3")

Range("R1").Value = Range("O4") & Range("M2")
Range("R2").Value = Range("O4") & Range("M3")

Range("S1").Value = Range("O3") & Range("M2")
Range("S2").Value = Range("O3") & Range("M3")

If Application.IsNA(Cells(5, 13)) Then

For I = M4 To rowcount9 - 2

Cells(I + 2, 10).Select

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

Next I

Else

For I = M4 To M5 - 3

Cells(I + 2, 10).Select
ActiveCell.FormulaR1C1 = "=INDEX(Indirect(r1c19):Indirect(r2c19),MATCH(RC[-8]&[RC-1],Indirect(r1c17):Indirect(r2c17)&Indirect(r1c18):Indirect(r2c18),0),1)"

Next I

End If

End Sub``````

Look at the "result i want to get". And maybe it make much clearer

#### tusharm

##### MrExcel MVP
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?
Since you've shared an image, I'm using a reorganized version of the problem. You should be able to easily adapt it to your need.

Suppose you want to look up 2 columns B and C and get the result from D:

 B C D 3​ R 1​ 10​ 4​ R 2​ 20​ 5​ G 1​ 30​ 6​ G 2​ 40​ 7​ B 2​ 50​ 8​ Y 1​ 60​

Also suppose the values you want to look up are in

 F G 6​ Y 1​

Then, in some cell use the formula: =INDEX(D3:D8,MATCH(F6&CHAR(255)&G6,B3:B8&CHAR(255)&C3:C8,0))

Tested with O365. In older versions of Excel you may have to array-enter the formula

#### Endered5

##### New Member

ADVERTISEMENT

Since you've shared an image, I'm using a reorganized version of the problem. You should be able to easily adapt it to your need.

Suppose you want to look up 2 columns B and C and get the result from D:

 B C D 3​ R 1​ 10​ 4​ R 2​ 20​ 5​ G 1​ 30​ 6​ G 2​ 40​ 7​ B 2​ 50​ 8​ Y 1​ 60​

Also suppose the values you want to look up are in

 F G 6​ Y 1​

Then, in some cell use the formula: =INDEX(D3:D8,MATCH(F6&CHAR(255)&G6,B3:B8&CHAR(255)&C3:C8,0))

Tested with O365. In older versions of Excel you may have to array-enter the formula
Hi,

Not entirely sure what this means. I think it would be easier if I upload the excel file. Is it possible? cant find any button for that. It seems i can only upload images

#### Endered5

##### New Member
Since you've shared an image, I'm using a reorganized version of the problem. You should be able to easily adapt it to your need.

Suppose you want to look up 2 columns B and C and get the result from D:

 B C D 3​ R 1​ 10​ 4​ R 2​ 20​ 5​ G 1​ 30​ 6​ G 2​ 40​ 7​ B 2​ 50​ 8​ Y 1​ 60​

Also suppose the values you want to look up are in

 F G 6​ Y 1​

Then, in some cell use the formula: =INDEX(D3:D8,MATCH(F6&CHAR(255)&G6,B3:B8&CHAR(255)&C3:C8,0))

Tested with O365. In older versions of Excel you may have to array-enter the formula
Just tried it and i got error. Any ideas+

#### jasonb75

##### Well-known Member
This is not a solution, it is a crude fix that appears to work.
VBA Code:
``ActiveCell.FormulaArray = "=INDEX(" & Range("S1").Value & ":" & Range("S2").Value & ",MATCH(B" & ActiveCell.Row & "&""|""&I" & ActiveCell.Row & "," &Range("Q1").Value & ":" & Range("Q2").Value & "&""|""&" & Range("R1").Value & ":" & Range("R2").Value & ",0))"``

• Endered5

#### Endered5

##### New Member
This is not a solution, it is a crude fix that appears to work.
VBA Code:
``ActiveCell.FormulaArray = "=INDEX(" & Range("S1").Value & ":" & Range("S2").Value & ",MATCH(B" & ActiveCell.Row & "&""|""&I" & ActiveCell.Row & "," &Range("Q1").Value & ":" & Range("Q2").Value & "&""|""&" & Range("R1").Value & ":" & Range("R2").Value & ",0))"``
Awesome! It works really well when I try it! What do you mean by it is not a solution?

However, the code seems a bit tricky and complex. Do you mind explaining it? Interested in understanding what's happening.

Replies
4
Views
360
Replies
10
Views
604
Replies
6
Views
471
Replies
4
Views
116
Replies
8
Views
283

Threads
1,127,994
Messages
5,628,034
Members
416,287
Latest member
wanji

### Share this page ### 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