Formula required to look up four values and return values from a Column

[sameer79]

Hello All,

I am using Excel 2013 and have a problem as shown in the attached sample data.

I have two sheets viz output and 10.02.2022 (this will change on daily basis).

In output sheet I need to pull up value from Cell S139 from the 10.02.2022 sheet as follows:

If Col A (Category) matches Cell A137 on 10.02.2022 sheet.
If Col B (Item) matches Cell B139 on 10.02.2022 sheet.
If Col C (Type) matches Cell H139 on 10.02.2022 sheet.

Then it should pull up the value from respective rows of Cell S139 till down of 10.02.2022 sheet.

As this will be part of daily progress report if the formula can take into consideration the cell M1 of Output sheet and Cell U3 of 10.02.2022 for updating.

I have colored for explanation purpose only.

Thanks in advance.

[wk9128]

worksheet Or Tab name : output

Cell M3 formula , Drag down

Please Login or Register  to view this content.

[JohnTopley]

Try

=IF(INDIRECT("'" & Sheets &"'!A137")=[@Category],SUMIFS(INDIRECT("'" & $M$1& "'!s139:s1000"),INDIRECT("'" & $M$1& "'!B139:B1000"),[@Item],INDIRECT("'" & $M$1& "'!H139:H1000"),[@Type]))

Change M1 to 10.02.2022 to match tab name

[sameer79]

Dear John,

Thanks for your time.

It gives #Name error.

Please see attached file.

[JohnTopley]

My error

=IF(INDIRECT("'" & $M$1 &"'!A137")=[@Category],SUMIFS(INDIRECT("'" & $M$1& "'!s139:s1000"),INDIRECT("'" & $M$1& "'!B139:B1000"),[@Item],INDIRECT("'" & $M$1& "'!H139:H1000"),[@Type]))

I used a named range "Sheets" so I have changed the formula

For you: learn how to use "Evaluate Formula" as this is a key tool in Excel

[sameer79]

Dear John,

Thanks for the revised formula.
It works great but I have an issue as I mentioned that the date will be changed on daily basis. I have made a new sample please see attached.

I have made a range name 'dynamic' and changed the formula accordingly. But obviously it is not working as you see in the sample.
Can it be more dynamic that it is pulling the values under appropriate dates on the output sheet?

FYI, there will be only one sheet for a day.

I hope it is clear.

Thanks once again for all your time and help.

[sameer79]

Thanks for your formula.
It works but I want it to be more dynamic in nature.

[JeteMc]

I have made a range name 'dynamic' and changed the formula accordingly. But obviously it is not working as you see in the sample.

The curse of Merge & Center strikes again.
Changing the "Refers to" of "dynamic" to ='10.02.2022'!$U$3 removes the #VALUE! error and populates the cells with numbers.
Let us know if you have any questions.

[sameer79]

Dear All,
Thanks for all the help.
Post marked SOLVED.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.