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

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,489
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 3, 2020
Messages
25
Office Version
  1. 2016
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
Joined
Dec 30, 2008
Messages
12,489
Office Version
  1. 365
Platform
  1. Windows
Please post that process and answer the other question in my previous post.
 

Endered5

New Member
Joined
Oct 3, 2020
Messages
25
Office Version
  1. 2016

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
 

Attachments

  • test11.JPG
    test11.JPG
    165.8 KB · Views: 6
  • Result i want to get.JPG
    Result i want to get.JPG
    161 KB · Views: 6

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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:



BCD
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


FG
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
Joined
Oct 3, 2020
Messages
25
Office Version
  1. 2016

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:



BCD
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


FG
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
Joined
Oct 3, 2020
Messages
25
Office Version
  1. 2016
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:



BCD
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


FG
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
Joined
Dec 30, 2008
Messages
12,489
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 3, 2020
Messages
25
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

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

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