Using multiple values to find another value

I am not quite sure how you want to display this.

First of all, I converted the data ranges into tables, because tables "know" how big they are and you can address columns by column header name. This makes formulas easier to understand and code easier to write.

I assumed that what makes a record unique is the combination of country and package, so I made a composite by combining both in each of the tables so I had something to match upon.

On Tab1 there is a Cell Selection event that runs the code any time any cell in the table is selected.

This code looks up the composite found for that row on Tab1 and looks for it in the composite column in Tab2. If it finds it, it displays the information on the first row in the Extractor Tab. If the value is not found, a pop-up box is displayed.

On the order tab you enter The order number, country, start and end date and you expect the formula to get the correct code. Is this what you want? If so, in your example for Afghanistan, which one of the two records do you want returned? Obviously the first one, but how did you make that decision?

Hey,

On the order tab I enter the order number, country, start and end date and want the formula to pull the correct code from the column "offering" in the "code" tab and enter it into the column "offering" in extractor based on the order "no.".

E.G If someone orders Afghanistan 3G it fills the extractor column "offering" with DHI_AF_DP_3GSpeed and if 4G DHI_AF_DP_4GSpeed as these are what I need to send to the supplier using the extractor.

I will repeat my question in different words.

On the Codes sheet there are two different records for Afghanistan on rows 2 and 3. When I enter Afghanistan in cell B2 on the Order List, how do I know which one of these two records to use? How do I know whether to select 3G or 4G and put it in cell E2?

I can see a way that if I enter Afghanistan in Cell B2 and I enter 3G in Cell E2 then I can look up DHI_AF_DP_3GSpeed and place it in cell F2.

The first thing I did was change the data ranges on the Order Sheet and Code Sheet to Excel Tables because Excel Tables "know" how big they are, and they copy down formulas automatically. You can add new cods to the code table and not have to change the formula on the order table. When you add a new order number, it becomes part of the order table and the formula for the offering is copied down automatically.

Also tables allow you to address columns by header name which makes understanding the formulas easier and even helps with VB coding.

Since it is the combination of Country and Code that makes a record unique, I created a composite key as a helper column in the code table: =TRIM([@Country])&":"&TRIM([@Code]) - this is nothing more than the two strings concatenated together. I usually stick some sort of delimiter (in this case a colon) between the parts just to make it more human readable. Also in this case, I had to add TRIM because some of the country or code names have trailing or leading blanks.

On the Order Sheet, I look up the record using MATCH on the composite key and INDEX =IFERROR(INDEX(Table_Codes,MATCH(TRIM([@Country])&":"&TRIM([@Code]),Table_Codes[Composite],0),3),"No Offering")

Here is some more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel

I also recommend adding data validation (it copies down automatically in tables too) to check:
- the proper number of digits are added for the order number
- Use a drop-down list validation for country and Code
- Use a date validation for Start and End Dates

Establish the validation in the first row, and then Copy -> Paste Special -> Validation for the rest of the rows. Once every row has the same validation, it will be added automatically to all new rows from then on.

Here is information on data validation: http://www.utteraccess.com/wiki/Data_Validation