Index/Match two columns with different rows and return 3rd column value

H

help4me

New Member
Joined
Oct 4, 2016
Messages
2
I am trying to index/match/lookup values from a Quickbooks report that appear on another sheet in two separate columns and on different rows that will return a value from a 3rd column. I have exported a report from Quickbooks that totals based on Class and Sub-Class. I have created a separate sheet of just the class and sub-classes for easier reference, though not sure if needed. The sheet below is re-created monthly with new values. Part of the problem, is that for every Class (B), there is the same sub-class name(C). Please reference the example below. I would like to be able to have a formula on a different sheet that would pull the value for each category and sub-category. Example: I want to lookup/match Construction (B) and Total Los Angeles(C) (under the Construction category) and return the value for Total Los Angeles (N). I would like another formula to do the same for the Contents category (B). I am trying to automate my sheet with these formulas so when I have to redo each month the formulas stay the same and it's just the data that changes on the sheet below and due to the formulas, it automatically pulls the new data. I cut and past from an Quickbooks export to Excel report into the sheet below. I hope this makes sense! Any help would be greatly appreciated! :smile:

[TABLE="width: 1495"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Num[/TD]
[TD]Name[/TD]
[TD]Memo[/TD]
[TD]Account[/TD]
[TD]Class[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Construction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Los Angeles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/02/2016[/TD]
[TD]2060670[/TD]
[TD]Test[/TD]
[TD]Construction:Los Angeles[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Construction:Los Angeles[/TD]
[TD][/TD]
[TD="align: right"]466.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Total Los Angeles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]466.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/02/2016[/TD]
[TD]2640[/TD]
[TD]Test[/TD]
[TD]Construction:Orange Deposit[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Construction:Orange[/TD]
[TD][/TD]
[TD="align: right"]585.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]585.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Riverside[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/30/2016[/TD]
[TD]VISA 4818[/TD]
[TD]Test[/TD]
[TD]Construction:Riverside (partial payment for draw #3)[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Construction:Riverside[/TD]
[TD][/TD]
[TD="align: right"]2,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Total Riverside[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]2,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]San Bernardino[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/02/2016[/TD]
[TD]2060587[/TD]
[TD]Test[/TD]
[TD]BOARD UP Construction:San Bernardino[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Construction:San Bernardino[/TD]
[TD][/TD]
[TD="align: right"]600.52[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Total San Bernardino[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]600.52[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]San Diego[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/12/2016[/TD]
[TD]0004053101[/TD]
[TD]Test[/TD]
[TD]Construction:San Diego[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Construction:San Diego[/TD]
[TD][/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Total San Diego[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Ventura[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/22/2016[/TD]
[TD]3518[/TD]
[TD]Test[/TD]
[TD]Construction:Ventura[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Construction:Ventura[/TD]
[TD][/TD]
[TD="align: right"]9,866.70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total Ventura[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]9,866.70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Total Construction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]13,528.26[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Contents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Los Angeles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/01/2016[/TD]
[TD]1614388634[/TD]
[TD]Test[/TD]
[TD]PO Contents:Los Angeles[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Contents:Los Angeles[/TD]
[TD][/TD]
[TD="align: right"]584.86[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Total Los Angeles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]584.86[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/01/2016[/TD]
[TD]1614388634[/TD]
[TD]Test[/TD]
[TD]PO Contents:Orange[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Contents:Orange[/TD]
[TD][/TD]
[TD="align: right"]2,334.38[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]2,334.38[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Riverside[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD="align: right"]09/01/2016[/TD]
[TD]1614388634[/TD]
[TD]Test[/TD]
[TD]PO Contents:Riverside[/TD]
[TD]12001 · Undeposited Funds[/TD]
[TD]Contents:Riverside[/TD]
[TD][/TD]
[TD="align: right"]3,348.22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Total Riverside[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]3,348.22[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

M
  • Question Question
Trying to delete rows that do not contain specific text
Replies
6
Views
826
MPH88
M
V
  • Question Question
Find value in one column and return value from same row but different column in another tab
2 3
Replies
23
Views
3K
Vickie Hartley
V

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top