lookup value

aarky

New Member
Joined
Jan 29, 2020
Messages
5
Office Version
  1. 2013
Platform
  1. Windows
how to solve this puzzle
 

Attachments

  • exceltest.png
    exceltest.png
    99.1 KB · Views: 7

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi & welcome to MrExcel.
How about

ZFluff.xlsm
DEFGHIJKLMNO
4QuarterMonthAmountQuarterMonthAmountQuarterMonthAmount
5qt1jan7890qt2apr3890qt2apr3890
6qt1fab8970qt2may8990
7qt1mar5670qt2jun7890
8QuarterMonthAmountQuarterMonthAmount
9qt3jul2899qt4oct9789
10qt3aug8765qt4nov6383
11qt3sep3948qt4dec8389
Main
Cell Formulas
RangeFormula
O5O5=SUMPRODUCT((D5:G11=M5)*(E5:H11=N5),F5:I11)
 
Upvote 1
Solution
Please insert the following function below in a Module. After you do this, go to cell O5 and type "=GetAmount(D4:I11,M5,N5)"

VBA Code:
Option Explicit
Function GetAmount(Rng1 As Range, qtr As String, month As String) As Variant
Dim cl As Object
    For Each cl In Rng1
        If cl.Value = qtr And cl.Offset(0, 1).Value = month Then
            GetAmount = cl.Offset(0, 2).Value
            Exit For
        Else
            Debug.Print cl.Value
            GetAmount = 0
        End If
    Next cl
End Function
 
Upvote 1

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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