Query a Cross Table according to Both Row Value and Column Value

Problem description & analysis

Below is Excel file book1.xlsx:

A

B

C

D

1

name

b

2

date

2000/1/2

3

price

4

5

6

date/price

a

b

c

7

2000/1/1

1

2

3

8

2000/1/2

4

5

6

9

2000/1/3

7

8

9

10

2000/1/4

10

11

12

11

2000/1/5

13

14

15

12

2000/1/6

16

17

18

We are trying to find the price value in the data table of A6:D12 according to B1’s name value and B2’s date value and enter the result into B3. For instance, when B1 is b and B2 is 2000/1/2, we get 5 for B3, as shown below:

A

B

C

D

1

name

b

2

date

2000/1/2

3

price

5

4

5

6

date/price

a

b

c

7

2000/1/1

1

2

3

8

2000/1/2

4

5

6

9

2000/1/3

7

8

9

10

2000/1/4

10

11

12

11

2000/1/5

13

14

15

12

2000/1/6

16

17

18

Solution and explanation

First, load add-in ExcelRaq.xll to Excel, and then

Enter the following formula in cell B3: =esproc("=?1.concat@nc().import@tc().select(#1==?2).field(?3)",A6:D12,B2,B1) to get the target result.

Q & A Collection

https://stackoverflow.com/questions/63655576/return-a-value-based-on-multiple-conditions-in-excel