Use OFFSET/INDIRECT/INDEX/MATCH (or any combo) to find value across multiple worksheets

K

kkrauseTX

New Member
Joined
Aug 20, 2013
Messages
2
I want to prepare a SUMMARY page that finds a corresponding ITEM# on the Summary page within the other 6 worksheet. The ITEM# will appear on ONCE on a single workbook and they will NOT be in alpha/numeric order (so LOOKUP/HLOOKUP/VLOOKUP won't work).

The formulas needed will need to match the ITEM # on the SUMMARY page that is contained with one of the six worksheets --- BREAD (1st worksheet) through VEGETABLES (last worksheet) and return:
1) The worksheet name where the ITEM# is found (green-colored section)
2) Use OFFSET to go down X rows to pull-in the Total Fees line and the X column for the respective month (peach-colored section)

The formula should be repeatable so that it can be replicated to all the cells w/in each respective colored section.

I'm sure there is a combo formula using OFFSET/INDIRECT/INDEX/MATCH somehow that will make this work but I'm coming up empty when trying to find it across multiple worksheets. Any help on this would be greatly appreciated.

:) Thanks.

Sample Data:

SUMMARY SHEET
Match the ITEM# on SUMMARY to find it in the other 3 worksheets (WS01, WS02, WS03) and return the Worksheet Name AND the Total Fees for the respective month.
NOTE: On the SUMMARY sheet, the ITEM # is hard-keyed in. Columns 2-6 need formulas to find and bring in the respective results.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM #[/TD]
[TD]Worksheet Name[/TD]
[TD]Jul-2016[/TD]
[TD]Aug-2016[/TD]
[TD]Sep-2016[/TD]
[TD]Oct-2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WB999[/TD]
[TD]WS01[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl63, width: 83"]AC111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WS02[/TD]
[TD]0[/TD]
[TD]600[/TD]
[TD]400[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]OR888[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WS03[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD]1100[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]WB111[/TD]
[TD]WS01[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]950[/TD]
[TD]750[/TD]
[/TR]
</tbody>[/TABLE]













WORKSHEET 1 named "WS01": (contains multiple items listed down the page)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM #[/TD]
[TD]WB999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul-2016[/TD]
[TD]Aug-2016[/TD]
[TD]Sep-2016[/TD]
[TD]Oct-2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Initial Fee[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Monthly Fee[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Other Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]TOTAL FEES[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]ITEM #[/TD]
[TD]WB111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul-2016[/TD]
[TD]Aug-2016[/TD]
[TD]Sep-2016[/TD]
[TD]Oct-2016[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Initial Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Monthly Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD]700[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Other Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]TOTAL FEES[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]950[/TD]
[TD]750[/TD]
[/TR]
</tbody>[/TABLE]




























WORKSHEET 2 is named "WS02": (contains multiple items listed down the page)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM #[/TD]
[TD]AC111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul-2016[/TD]
[TD]Aug-2016[/TD]
[TD]Sep-2016[/TD]
[TD]Oct-2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Initial Fee[/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Monthly Fee[/TD]
[TD][/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Other Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]TOTAL FEES[/TD]
[TD]0[/TD]
[TD]600[/TD]
[TD]400[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]ITEM #[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul-2016[/TD]
[TD]Aug-2016[/TD]
[TD]Sep-2016[/TD]
[TD]Oct-2016[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Initial Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Monthly Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Other Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]TOTAL FEES[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]





























WORKSHEET 3 is named "WS03": (contains multiple items listed down the page)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM #[/TD]
[TD]OR888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul-2016[/TD]
[TD]Aug-2016[/TD]
[TD]Sep-2016[/TD]
[TD]Oct-2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Initial Fee[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Monthly Fee[/TD]
[TD][/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Other Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]TOTAL FEES[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD]1100[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]ITEM #[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul-2016[/TD]
[TD]Aug-2016[/TD]
[TD]Sep-2016[/TD]
[TD]Oct-2016[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Initial Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Monthly Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Other Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]TOTAL FEES[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You must log in or register to reply here.

Similar threads

D
  • Solved
Get last 2 non blank values of a row
2
Replies
17
Views
1K
Fluff
Fluff
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
694
MrDB4Excel
MrDB4Excel
S
  • Question Question
Multiple Criteria Multiple Worksheets Find Match Lookup place on Summary Page
Replies
0
Views
757
SunshineLostHopeNExcel
S
H
  • Question Question
Having trouble calendarizing a utility bill across multiple months. Is there a quicker method than what I've done so far?
Replies
1
Views
644
helnuma2k
H
M
  • Question Question
Index Match Return Column # based on Sum of Columns
Replies
2
Views
930
mkrecknagel
M

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

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