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.
https://stackoverflow.com/questions/63655576/return-a-value-based-on-multiple-conditions-in-excel
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
