Index/Match

[SteveOranjinSteve]

Hello,

Hope you're all well. It's been over a year since I've hopped on here. I've used Vlookup A lot... I'm familiar with the fact that Index/Match is superior, but I've never had a need for it before. Here is the situation...

"Table entitled - 'Restricted_Table'"

JDE Code (A1)	Country Code (B1)	Restriction?(C1)	Country(D1)
1123567 (A2)	AD (B2)	Regulatrory Restrionction.... (C2)	Andorra (D2)
12345 (A3)	VA (B3)	Regulatory Restriction (C3)	Vatican City (D3)

Table I'm working from. (These are both on different tabs by the way.)

Number (A1)	Country (B1)	Local Registration License Number (Equivalent to JDE Code) (C1)	Registration Expiration Date (D1)	Restriction (E1)
1 (A1)	Mexico (B1)	1123567 (C1)	7/31/2023 (D2)	=INDEX(Restricted_Table,MATCH(1,(Restricted_Table=D3)*(Restricted_Table!E3:E4574),0),3) (E2)
2	Andorra	1123567	7/6/2022
3	Vatican City	12345	8/25/2019

The formula I have entered I do not think is even remotely close. If anyone could help me understand what I must do, so that I can match the contents of table 1 to the contents of table 2 on the criterion of BOTH COUNTRY and JDE Code, I would be greatly in your debt. There are multiple JDE Codes, which I have provided a small sample of.

The formula is the one I have just used, the tables do not correspond perfectly to what I am attempting to do. Please let me know if there is any other information you need. Hope you are well!

Steve

 

[teylyn]

Hello,

this is a lookup with two parameters. You need to combine the Country and the JDE to the lookup value and also combine the two columns in the lookup table with another Index. Something like this

=INDEX(Restricted_Table[Restriction?],MATCH(Sheet3!C2&Sheet3!B2,INDEX(Restricted_Table[JDE Code]&Restricted_Table[Country],0),0))

 

[Sequoyah]

Hi Steve,
try with an Array Formula in cell E2, must be entered by pressing Ctrl+Shift+Enter, the formula bar will show the formula surrounded by curly brackets {=...}
=INDEX(Restricted_Table,MATCH(1,((Restricted_Table[Country])=B2)*((Restricted_Table[JDE Code])=C2),0),3)

 

[DanteAmor]

Another way with a "regular" formula

=LOOKUP(2,1/((Restricted_Table[Country]=B2)*(Restricted_Table[JDE Code]=C2)),Restricted_Table[Restriction])