Phoneix asks:

I have a worksheet(#1) and I want to populate the amount Column with data in another worksheet in the same workbook, Based on 2 criteria: State and Date
Worksheet #2 Table A1:D19 = EST

ColA ColB ColC ColD
1 AK 5/31 1st Installment 53,397.00
2 AK 8/31 2nd Installment 53,397.00
3 AK 11/30 3rd Installment -
4 AL 5/15 1st Installment -
5 AL 8/15 2nd Installment 53,349.00
6 AL 11/15 3rd Installment -
7 AR 5/15 1st Installment 33,237.00
8 AR 8/15 2nd Installment 33,237.18
9 AR 11/15 3rd Installment -
10 AZ 3/15 1st Installment 62,738.89
11 AZ 4/15 2nd Installment 62,738.89
12 AZ 5/15 3rd Installment 62,738.89
13 AZ 6/15 4th Installment 137,986.05
14 AZ 7/15 5th Installment 81,550.68
15 AZ 8/15 6th Installment 81,550.68
16 CA 4/1 1st Installment 495,364.00
17 CA 6/1 2nd Installment 495,364.00
18 CA 9/1 3rd Installment 495,364.00
19 CA 12/1 4th Installment -
Worksheet #1 I need the Worksheet 2 ColD amount to be put in the corresponding State and Date ColE on this worksheet. Can you help?

ColA ColB ColC ColD ColE
1 AK 5/31 -
2 AK 8/31 -
3 AK 11/30 -
4 AL 5/15 -
5 AL 8/15 -
6 AL 11/15 -
7 AZ 3/15 -
8 AZ 4/15 -
9 AZ 5/15 -
10 AZ 6/15 -
11 AZ 7/15 -
12 AZ 8/15 -
13 AR 5/15 -
14 AR 8/14 -
15 AR 11/14 -
16 CA 4/1 -
17 CA 6/1 -
18 CA 9/1 -
19 CA 12/1 -

Answer:

Sheet2

Sheet1

Array formula in cell C2, sheet1:

=INDEX(Table1[Column4],MATCH(1,COUNTIFS([@Column1],Table1[Column1],[@Column2],Table1[Column2]),0))

How to create an array formula

  1. Select cell C2
  2. Click in formula bar
  3. Copy/Paste above array formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

Explaining array formula in cell C2, sheet1:

Step 1 - Find rows where both critera match

COUNTIFS([@Column1],Table1[Column1],[@Column2],Table1[Column2])

becomes

COUNTIFS("AK",{"AK"; "AK"; "AK"; "AL"; "AL"; "AL"; "AR"; "AR"; "AR"; "AZ"; "AZ"; "AZ"; "AZ"; "AZ"; "AZ"; "CA"; "CA"; "CA"; "CA"},41060,{41060; 41152; 41243; 41044; 41136; 41228; 41044; 41136; 41228; 40983; 41014; 41044; 41075; 41105; 41136; 41000; 41061; 41153; 41244})

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

Step 2 - Find relative row number

MATCH(1,COUNTIFS([@Column1],Table1[Column1],[@Column2],Table1[Column2]),0)

becomes

MATCH(1,{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0},0)

and returns 1.

Step 3 - Return a value of the cell at the intersection of a particular row and column

INDEX(Table1[Column4],MATCH(1,COUNTIFS([@Column1],Table1[Column1],[@Column2],Table1[Column2]),0))

becomes

INDEX(Table1[Column4],1)

becomes

INDEX({" 53,397.00"; " 53,397.00"; " -"; " -"; " 53,349.00"; " -"; " 33,237.00"; " 33,237.18"; " -"; " 62,738.89"; " 62,738.89"; " 62,738.89"; " 137,986.05"; " 81,550.68"; " 81,550.68"; " 495,364.00"; " 495,364.00"; " 495,364.00"; " -"},1)

and returns 53,397.00 in cell C2, sheet1.

Download excel *.xlx file

Fetch data from another table.xlsx

Related posts:

How to use a table name in data validation lists and conditional formatting formulas

Change pivot table data source using a drop down list

Search for a cell value in an excel table

Populate a list box with visible unique values from an excel table (vba)

Locate lookup values in a table [HYPERLINK]