Excel VBA VLOOKUP with 2 criterias with parameters in rows

[sofia_k]

Hello,

I need help with VLOOKUP in VBA that i havent figured out for weeks now (but im still a beginner too).

I have file with 2 sheets - data and calculation.
Data sheet has base data as column A has metric name, columns B to O has values
but the catch is that criteria values are in rows 1 and 2 - date and country
fe.
A B C D
01.11.2023 01.12.2023 01.01.2024
NO FI CA
Total salary 520835 4220170 7187268
GGR 112004 529309 334859

Sheet calculation has dates in column A, countries in Column B and metrics in first row of columns C to J
A B C D
DATE COUNTRY Total salary GGR
01.11.2023 NO
01.12.2023 FI

So i would need it to find fe. total salary value to calculation sheet (C2) based on date and country given in columns a and b and to loop it until the end of values given in columns a/b. I couldnt find any version where vloopup would search from rows as criteria, also i couldnt make index match work so help!

 

[DanteAmor]

Hi @sofia_k. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

If I understand correctly, your data in the DATA sheet is like this:

Dante Amor
	A	B	C	D
1		01.11.2023	01.12.2023	01.01.2024
2		NO	FI	CA
3	Total salary	520,835.00	4,220,170.00	7,187,268.00
4	GGR	112004	529309	334859
Data

So with the SUMPRODUCT function, you can get the results in the calculation sheet, like this:

Dante Amor
	A	B	C	D
1	DATE	COUNTRY	Total salary	GGR
2	01.11.2023	NO	520,835.00	112004
3	01.12.2023	FI	4,220,170.00	529309
4	01.01.2024	CA	7,187,268.00	334859
calculation

Cell Formulas
Range	Formula
C2:C4	C2	=SUMPRODUCT((Data!$B$1:$O$1=$A2)*(Data!$B$2:$O$2=$B2)*(Data!$B$3:$O$3))
D2:D4	D2	=SUMPRODUCT((Data!$B$1:$O$1=$A2)*(Data!$B$2:$O$2=$B2)*(Data!$B$4:$O$4))

If you want the macro in VBA then it could be like this:

Sub vlookup2criteras()
  With Sheets("calculation").Range("C2:C" & Sheets("calculation").Range("A" & Rows.Count).End(3).Row)
    .Formula = "=SUMPRODUCT((Data!$B$1:$O$1=$A2)*(Data!$B$2:$O$2=$B2)*(Data!$B$3:$O$3))"
    .Value = .Value
  End With
  With Sheets("calculation").Range("D2:D" & Sheets("calculation").Range("A" & Rows.Count).End(3).Row)
    .Formula = "=SUMPRODUCT((Data!$B$1:$O$1=$A2)*(Data!$B$2:$O$2=$B2)*(Data!$B$4:$O$4))"
    .Value = .Value
  End With
End Sub

---------------
Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------