Index/Match formula for multiple values and columns with uneven length

[reporting_95]

Hi,
This appeared to be a simple vlookup and match task but I kind of ran into a roadblock.

I'm working on a financial report that shows amounts of aid students will receive for the year and semester. In sheet 1, I have a list of students that are coded with a unique ID number and an item type number that represents the type of aid they're receiving for that year (example: 1001=SubLoan1,1002=UnSubLoan1,1003=Grant ect.), along with the dollar amount.

In sheet 2, I have the same population of students but it is filtered to only focus on the loan item type and loan amount they will receive in a specific semester (Fall). What I want to happen is display the loan amount for the fall semester in the sheet 1, which lists all item types of aid for the year, from the data in sheet 2, which only focuses on loans for the semester.

The issue I'm running into is finding a way to have excel to use both the unique id and item type id in sheet 1 to find the same values in sheet 2, and return the loan amount to sheet 1.

Another issue that I have is the columns in both sheets are uneven in length. This is due to more rows in sheet 1 listing all item types of aid, as opposed to the columns in sheet 2 which only focuses on the item types of loans. I tried vlookup but that can only find one value at a time, and index/match formulas i used return #NA and # VALUE! errors.

Can someone please help me out with this?

Thank you in advance!

 

[DanteAmor]

You could give examples of what you have on both sheets and what you expect from the result. Explain a couple of examples.

 

[reporting_95]

Sheet 1 would look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Last
[/TD]
[TD]First
[/TD]
[TD]Item Type#
[/TD]
[TD]Description
[/TD]
[TD]Yearly Amount
[/TD]
[TD]Fall Amount
[/TD]
[TD]Spring Amount
[/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD]1,000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1002
[/TD]
[TD]UnSubLoan2
[/TD]
[TD]800
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD]2,000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1003
[/TD]
[TD]Grant
[/TD]
[TD]600
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Last
[/TD]
[TD]First
[/TD]
[TD]Item Type #
[/TD]
[TD]Description
[/TD]
[TD]Fall Amount
[/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1002
[/TD]
[TD]UnSubLoan2
[/TD]
[TD]400
[/TD]
[/TR]
</tbody>[/TABLE]

Essentially, Sheet 1 is the Master report that is showing all of the information plus the loan amounts broken down by year and semester. When Sheet 1 is completed with the Fall data, it should look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Last
[/TD]
[TD]First
[/TD]
[TD]Item Type#
[/TD]
[TD]Description
[/TD]
[TD]Yearly Amount
[/TD]
[TD]Fall Amount
[/TD]
[TD]Spring Amount
[/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD]1,000
[/TD]
[TD]500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1002
[/TD]
[TD]UnSubLoan2
[/TD]
[TD]800
[/TD]
[TD]400
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD]2,000
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1003
[/TD]
[TD]Grant
[/TD]
[TD]600
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table> <table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:46.57px;" /><col style="width:40.87px;" /><col style="width:34.22px;" /><col style="width:72.24px;" /><col style="width:78.89px;" /><col style="width:94.1px;" /><col style="width:77.94px;" /><col style="width:94.1px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ID</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Last</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">First</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Item Type#</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Description</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Yearly Amount</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Fall Amount</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Spring Amount</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">552343</td><td >Smith</td><td >John</td><td style="text-align:right; ">1001</td><td >SubLoan1</td><td style="text-align:right; ">1000</td><td style="text-align:right; ">500</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">552343</td><td >Smith</td><td >John</td><td style="text-align:right; ">1002</td><td >UnSubLoan2</td><td style="text-align:right; ">800</td><td style="text-align:right; ">400</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">552343</td><td >Smith</td><td >John</td><td style="text-align:right; ">1001</td><td >SubLoan1</td><td style="text-align:right; ">2000</td><td style="text-align:right; ">500</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">552343</td><td >Smith</td><td >John</td><td style="text-align:right; ">1003</td><td >Grant</td><td style="text-align:right; ">600</td><td style="text-align:right; ">0</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=SUMPRODUCT((Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2:$C$100=C2)*(Sheet2!$D$2:$D$100=D2)*(Sheet2!$E$2:$E$100=E2)*(Sheet2!$F$2:$F$100))</td></tr></table></td></tr></table>

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table> <table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:46.57px;" /><col style="width:40.87px;" /><col style="width:34.22px;" /><col style="width:75.09px;" /><col style="width:78.89px;" /><col style="width:77.94px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ID</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Last</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">First</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Item Type #</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Description</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Fall Amount</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">552343</td><td >Smith</td><td >John</td><td style="text-align:right; ">1001</td><td >SubLoan1</td><td style="text-align:right; ">500</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">552343</td><td >Smith</td><td >John</td><td style="text-align:right; ">1002</td><td >UnSubLoan2</td><td style="text-align:right; ">400</td></tr></table>

In your example Id 552343, it is repeated 2 times with the same name and with the same Item Type #, that is why the result in cells G2 and G4 is the same.
You can comment if the above is correct.

 

[DanteAmor]

If the ID is unique and also the Item Type #, then the formula can be reduced to this:

=SUMPRODUCT((Sheet2!$A$2:$A$100=A2)*(Sheet2!$D$2:$D$100=D2)*(Sheet2!$F$2:$F$100))

 

[reporting_95]

Thanks for the formula. It does work for the example I posted. As you mentioned, the item type of 1001 SubLoan1 appears twice in Sheet1. This is because the loan amounts are displayed by year, not semester. There isn't a way to tell the loan amount for a semester by looking at Sheet1, that is why I added the Fall and Spring columns. It should essentially skip over the second 1001 SubLoan1 option in Sheet1, because we are assuming that second option is for Spring. It can add a 0 or even an #N/A. I'm not sure how Excel would know that the Item Type is already filled in a previous cell.

 

[reporting_95]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Item Type #[/TD]
[TD]Description[/TD]
[TD]Fall Amount[/TD]
[TD]Spring Amount[/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]500[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1002[/TD]
[TD]UnSubLoan2[/TD]
[TD]400[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

So maybe this is a better example of Sheet2

 

[DanteAmor]

Try this in cell G2. Copy to H2 and both copy down

=IF(COUNTIFS($A$2:$A2,$A2,$D$2:$D2,$D2)=1,SUMPRODUCT((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$D$2:$D$100=$D2)*(Sheet2!F$2:F$100)),0)

 

[reporting_95]

Looks like the formula works great! The formula seems to be working as designed, but its adding up the some of the loan amounts because it sees the same item type in Sheet1. It should be displaying the broken down loan amount from Sheet2. But it also looks like i've run into another problem. The data from Sheet2 seems to be repeating item types because some students are receiving 2 deposits in the same semester. This should reflect in Sheet1 but it doesn't because there isn't an extra row for that student in the sheet. I'll show below:
Sheet1
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Last [/TD]
[TD]First[/TD]
[TD]Item Type#[/TD]
[TD]Description[/TD]
[TD]Yearly Amount[/TD]
[TD]Fall Amount[/TD]
[TD]Spring Amount
[/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]1,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1002[/TD]
[TD]UnSubLoan2[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD]2,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1003[/TD]
[TD]Grant[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Last[/TD]
[TD]First
[/TD]
[TD]Deposit ID
[/TD]
[TD]Item Type #
[/TD]
[TD]Description[/TD]
[TD]Fall Amount
[/TD]
[TD]Spring Amount
[/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]2
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD]600
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1
[/TD]
[TD]1003
[/TD]
[TD]UnSubLoan2
[/TD]
[TD]1000
[/TD]
[TD]1000
[/TD]
[/TR]
</tbody>[/TABLE]
As you can see in the above example, Sheet1 only has 1 column for 1001, but in Sheet2, there are 2 columns that display the breakdown of the the amount.

So the Completed Sheet1 would look like this:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Last [/TD]
[TD]First[/TD]
[TD]Item Type#[/TD]
[TD]Description[/TD]
[TD]Yearly Amount[/TD]
[TD]Fall Amount[/TD]
[TD]Spring Amount[/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith[/TD]
[TD]John
[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD][/TD]
[TD]500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1001
[/TD]
[TD]SubLoan1
[/TD]
[TD][/TD]
[TD]500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55234
[/TD]
[TD]Smith
[/TD]
[TD]John
[/TD]
[TD]1002
[/TD]
[TD]UnSubLoan2
[/TD]
[TD]2000
[/TD]
[TD]1000
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]552343
[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1003[/TD]
[TD]Grant[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'm guessing I may have to add the deposit ID column in Sheet1 and then merge both sheets to get the equal number of entries in Sheet1. Then run a formula to find the corresponding amount by semester, by item type and deposit ID. Is this at all possible?

 

[DanteAmor]

Looks like the formula works great! The formula seems to be working as designed, but its adding up the some of the loan amounts because it sees the same item type in Sheet1. It should be displaying the broken down loan amount from Sheet2. But it also looks like i've run into another problem. The data from Sheet2 seems to be repeating item types because some students are receiving 2 deposits in the same semester. This should reflect in Sheet1 but it doesn't because there isn't an extra row for that student in the sheet. I'll show below:
Sheet1
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Item Type#[/TD]
[TD]Description[/TD]
[TD]Yearly Amount[/TD]
[TD]Fall Amount[/TD]
[TD]Spring Amount[/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]1,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1002[/TD]
[TD]UnSubLoan2[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]2,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1003[/TD]
[TD]Grant[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Deposit ID[/TD]
[TD]Item Type #[/TD]
[TD]Description[/TD]
[TD]Fall Amount[/TD]
[TD]Spring Amount[/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]2[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]1003[/TD]
[TD]UnSubLoan2[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]
As you can see in the above example, Sheet1 only has 1 column for 1001, but in Sheet2, there are 2 columns that display the breakdown of the the amount.

So the Completed Sheet1 would look like this:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Item Type#[/TD]
[TD]Description[/TD]
[TD]Yearly Amount[/TD]
[TD]Fall Amount[/TD]
[TD]Spring Amount[/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith [/TD]
[TD]John[/TD]
[TD]1001[/TD]
[TD]SubLoan1[/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55234[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1002[/TD]
[TD]UnSubLoan2[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]552343[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]1003[/TD]
[TD]Grant[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'm guessing I may have to add the deposit ID column in Sheet1 and then merge both sheets to get the equal number of entries in Sheet1. Then run a formula to find the corresponding amount by semester, by item type and deposit ID. Is this at all possible?

Okay, you should put more references on sheet 2, that way we can find the unique records.
If you are designing your sheets, then add other columns.
For example, instead of having 2 columns (fall and spring), I recommend having a column (semester) and divide the records one record for fall and another record for spring