Index Match with 2 columns

[RAJESH1960]

Hello guys
I need your help to enter a formula in column F in the 2B sheet, so that I can get the correct result. I tried using the vlookup function but it is not giving the right result.
In this workbook, the Invoice numbers are the same in some cases but of a different GSTIN number. I am trying to get the name of the Trade Name in column F where the GSTIN and Invoice number of B2B match with 2B.

Query match 2 columns and get value.xlsx
	A	B	C	D	E	F
5	GSTIN of supplier	Trade/Legal name of the Supplier	Invoice details
6			Invoice number	Invoice type	Invoice Date	Invoice Value (₹)
7	29AACCM4684P1ZN	January	1124T081711402	R	01-08-2017	9209
8	29AACCM4684P1ZN	January	1124T081711402	R	01-08-2017	9209
9	29AACCM4684P1ZN	January	1124T081711402	R	01-08-2017	9209
10	29AACCM4684P1ZN	January	1124T081711402	R	01-08-2017	9209
11	29AACCM4684P1ZN	January	1124T081711402	R	01-08-2017	9209
12	29AACCM4684P1ZN	January	1124T081711402	R	01-08-2017	9209
13	29AACCM4684P1ZN	January	1124T081711402-Total	R	01-08-2017	9209
14
15	33AACCM4684P1ZN	Sunday	1124	R	02-08-2017	7680
16	33AACCM4684P1ZN	Sunday	1124	R	02-08-2017	7680
17	33AACCM4684P1ZN	Sunday	1124	R	02-08-2017	7680
18	33AACCM4684P1ZN	Sunday	1124	R	02-08-2017	7680
19	33AACCM4684P1ZN	Sunday	1124	R	02-08-2017	7680
20	33AACCM4684P1ZN	Sunday	1124-Total	R	02-08-2017	7680
21
22	29AACCM4681P1ZN	August	GST - 1124	R	03-08-2017	2038
23	29AACCM4681P1ZN	August	GST - 1124-Total	R	03-08-2017	2038
24
25	29AACCM4684P1XXN	February	Aug - 0817	R	04-08-2017	4280
26	29AACCM4684P1XXN	February	Aug - 0817	R	04-08-2017	4280
27	29AACCM4684P1XXN	February	Aug - 0817-Total	R	04-08-2017	4280
28
29	29AACCM3684P1ZN	March	1124	R	04-08-2017	4958
30	29AACCM3684P1ZN	March	1124	R	04-08-2017	4958
31	29AACCM3684P1ZN	March	1124	R	04-08-2017	4958
32	29AACCM3684P1ZN	March	1124-Total	R	04-08-2017	4958
33
34	26AACCM4684P1ZN	April	123456	R	06-08-2017	5262
35	26AACCM4684P1ZN	April	123456	R	06-08-2017	5262
36	26AACCM4684P1ZN	April	123456	R	06-08-2017	5262
37	26AACCM4684P1ZN	April	123456-Total	R	06-08-2017	5262
38
39	30AACCM4684P1ZN	May	GST 0001	R	07-08-2017	4280
40	30AACCM4684P1ZN	May	GST 0001	R	07-08-2017	4280
41	30AACCM4684P1ZN	May	GST 0001-Total	R	07-08-2017	4280
42
43	31AACCM4684P1XN	June	1124T08	R	08-08-2017	2288
44	31AACCM4684P1XN	June	1124T08-Total	R	08-08-2017	2288
45
46	33AACCM4684P1ZN	Sunday	78610	R	11-08-2017	4280
47	33AACCM4684P1ZN	Sunday	78610	R	11-08-2017	4280
48	33AACCM4684P1ZN	Sunday	78610	R	11-08-2017	4280
49	33AACCM4684P1ZN	Sunday	78610-Total	R	11-08-2017	4280
50
51	29AXCCM4684P1ZN	December	GST 0001	R	12-08-2017	5198
52	29AXCCM4684P1ZN	December	GST 0001	R	12-08-2017	5198
53	29AXCCM4684P1ZN	December	GST 0001-Total	R	12-08-2017	5198
54
55	33AACCM4684P1ZN	Sunday	741258	R	14-08-2017	4280
56	33AACCM4684P1ZN	Sunday	741258-Total	R	14-08-2017	4280
57
58	33AACCM4684P1ZN	Sunday	12	R	17-08-2017	1030
59	33AACCM4684P1ZN	Sunday	12-Total	R	17-08-2017	1030
B2B

Query match 2 columns and get value.xlsx
	A	B	C	D	E	F	G	H
1	GSTIN	Date	B	Invoice number	X	Name	amount	Expected
2	29AACCM4684P1ZN	01-08-2017		1124T081711402		January	9209
3	33AACCM4684P1ZN	02-08-2017		1124		Sunday	7680
4	29AACCM4681P1ZN	03-08-2017		GST - 1124		August	2038
5	29AACCM4684P1XXN	04-08-2017		Aug - 0817		February	4280
6	29AACCM3684P1ZN	02-08-2017		1124		Sunday	7680	4958
7	26AACCM4684P1ZN	06-08-2017		123456		April	5262
8	30AACCM4684P1ZN	07-08-2017		GST 0001		May	4280
9	31AACCM4684P1XN	08-08-2017		1124T08		June	2288
10	33AACCM4684P1ZN	11-08-2017		78610		Sunday	4280
11	29AXCCM4684P1ZN	07-08-2017		GST 0001		May	4280	5198
12	33AACCM4684P1ZN	14-08-2017		741258		Sunday	4280
13	33AACCM4684P1ZN	17-08-2017		12		Sunday	1030
2B

Cell Formulas
Range	Formula
A2:A13	A2	=IFERROR(INDEX(B2B!$A$7:$A$100,AGGREGATE(15,6,ROW($B$1:$B$96)/(RIGHT(B2B!$C$7:$C$100,5)="Total"),ROWS($1:1))),"")
B2:B13	B2	=VLOOKUP(IF(LEN(D2)<7,VALUE(D2),D2),B2B!$C$8:$E$98,3,0)
F2:F13	F2	=VLOOKUP(D2,CHOOSE({1,2},B2B!C8:C86,B2B!B8:B86),2,0)
G2:G13	G2	=VLOOKUP(D2,B2B!$C$8:$F$86,4,0)
D2:D13	D2	=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX(B2B!$C$7:$C$100,AGGREGATE(15,6,ROW($B$1:$B$100)/(RIGHT(B2B!$C$7:$C$100,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX(B2B!$C$7:$C$100,AGGREGATE(15,6,ROW($B$1:$B$100)/(RIGHT(B2B!$C$7:$C$100,5)="Total"),ROWS($1:1))),"-Total","")),"")

 

[Anthony47]

Try in 2B!G2:

=INDEX(B2B!$F$1:$F$2000,MATCH(A6&D6,B2B!$A$1:$A$2000&B2B!$C$1:$C$2000,0))

To be confirmed using Contr-Shift-Enter, not Enter alone; then copy down

 

[RAJESH1960]

Try in 2B!G2:

=INDEX(B2B!$F$1:$F$2000,MATCH(A6&D6,B2B!$A$1:$A$2000&B2B!$C$1:$C$2000,0))

To be confirmed using Contr-Shift-Enter, not Enter alone; then copy

Anthony47. Thanks. The formula is working in column G. I wanted the formula for column F in 2B sheet (Get names). I tried to edit your formula and use the same in column F but I am getting January in all the rows.
The formula for column G is workin. I had to edit the range as the range in B2B sheet starts from the 7th rows. I tried this in column F

=INDEX(B2B!$B$7:$B$2000,MATCH(A2&D2,B2B!$A$7:$A$2000&B2B!$C$7:$C$2000,0))

Got both right now. Thanks once again.

 

[Anthony47]

Got both right now. Thanks once again.

So I understand that you fixed the formula for your need (mine was probably wrong in the part "A6&D6", as I erroneously copied the formula for G6 and not for G2)
Thank you for the feedback

 

[RAJESH1960]

So I understand that you fixed the formula for your need (mine was probably wrong in the part "A6&D6", as I erroneously copied the formula for G6 and not for G2)
Thank you for the feedback

Your formula helped me to get the correct result. Your formula was perfect except for the range which I edited it.