Look up 2 conditions in 2 sheets

colzre

New Member
Joined
Jan 8, 2024
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hello experts,

I am trying to look up 2 conditions in 2 different sheets.

Formula is in cell D13 on "Numbers" sheet to get 1st condition based on F13 and look for it on sheet "BUDGET 2024" cells X30:X37. 2nd condition based on "Numbers" sheet cell B1 and look for it on sheet "BUDGET2024" cells B1:M1.

Then return the data from the table on "BUDGET 2024" sheet cells B17:M42 when both conditions are met.

This would return the amount of 228,836.54 which sits on "BUDGET 2024" cell B30 however I get an error.

All cells in questions are highlighted in yellow background.


Any help would be highly appreciated.


Thanks,

John
 

Attachments

  • BUDGET 2024 sheet.PNG
    BUDGET 2024 sheet.PNG
    77 KB · Views: 5
  • Numbers sheet.PNG
    Numbers sheet.PNG
    44.8 KB · Views: 5

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
What is your formula and what is your error? Also, you say row 30, which has "Room Expenses" but then ask about "Room Revenue".

It would be HUGE help if you'd use the xl2bb add in (Link Below) and post a mini worksheet of your data. Posting images means the forum needs to recreate your data. And your data is all over the page and the forum could have typos and other error that can lead to confusion by you and the forum. If you cannot use the add in, then post your data as a table please, but tell us the top right cell address of the table so we can build the formula with matching cell references.

Thanks in advance.
 
Upvote 0
Thank you for your response.

I have amended Room Revenue for Room expenses as meant to be. Apologies. See the Mini Sheets please.

file.xlsx
ABCDEF
11
2
3
4Jan-24
5CurrentBudget
6
7
8
9
10
11
12Actual%Budget%
13#VALUE!Room expenses
Numbers
Cell Formulas
RangeFormula
D13D13=+XLOOKUP(F13,'BUDGET 2024'!X30:X39,XLOOKUP(Numbers!B1,'BUDGET 2024'!B1:M1,'BUDGET 2024'!B7:M86))
Cells with Data Validation
CellAllowCriteria
B4:E4List=$BP$1:$BP$12


file.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1123456789101112
2Statement of Income - Budget
3Rolling 12 Month Budcast
420/01/2024
5BudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastProjectionHideFilter
6Jan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24Nov-24Dec-24Totaldic 2023 T12dic 2022 T121
7Statement of Income
8Rooms Available202020202020202020202020240.001
9Rooms Sold202020202020202020202020240.001
10ADR202020202020202020202020240.001
11Occupancy%202020202020202020202020240.001
12RevPAR202020202020202020202020240.001
13Total RevPAR202020202020202020202020240.001
14
15REVENUE1
16
17Room Revenue202020202020202020202020240.001
18Food Revenue202020202020202020202020240.000
19Beverage Revenue202020202020202020202020240.00
20Beach Club Revenue (Only for GHI)202020202020202020202020240.00
21Events Revenue202020202020202020202020240.000
22Parking Revenue202020202020202020202020240.000
23Spa Revenue202020202020202020202020240.00
24Miscellaneous Revenue202020202020202020202020240.00
25TOTAL REVENUE160.00160.00160.00160.00160.00160.00160.00160.00160.00160.00160.00160.001,920.001
26
27
28EXPENSES1
29
30Room Expenses228,836.54189,465.30316,821.67428,821.00524,121.00535,665.00444,665.00402,421.40522,421.00449,821.00242,621.00226,665.004,512,344.91Room Expenses
31Food Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.001Food and Beverage Expenses
32Beverage Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000Food and Beverage Expenses
33Beach Club Expenses (Only for GHI)20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.00
34Events Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000
35Parking Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000Parking Expenses
36Spa Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.00SPA Expenses
37Miscellaneous Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000Other Income Expenses
38Departmental Expenses228,976.54189,605.30316,961.67428,961.00524,261.00535,805.00444,805.00402,561.40522,561.00449,961.00242,761.00226,805.004,514,024.911
39
40Administrative and General20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00513,068.921
41Info and Telecom Systems20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0029,403.101
42Sales and Marketing20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00108,668.951
BUDGET 2024
Cell Formulas
RangeFormula
N8:N13,N30:N38,N17:N25N8=SUM(B8:M8)
B25:M25,B38:M38B25=SUM(B17:B24)
 
Upvote 0
Try this formula instead:

Excel Formula:
=INDEX('Budget 2024'!$C$8:$M$42,
MATCH($F13,'Budget 2024'!$A$8:$A$42,0),
MATCH($B$1,'Budget 2024'!$B$1:$M$1,0))
 
Upvote 0
Try this formula instead:

Excel Formula:
=INDEX('Budget 2024'!$C$8:$M$42,
MATCH($F13,'Budget 2024'!$A$8:$A$42,0),
MATCH($B$1,'Budget 2024'!$B$1:$M$1,0))
That´s perfect!!

Thank you very much.

Only problem I have got now that once I continue with document I get the same name in different cells "Room Expenses" so it doesn´t return the all the values since it should be only "Room Expenses" once for the whole thing.

Could the SumIf function work in this scenario? Same thing but to sum up all the lines every time the condition repeats.

Not too sure how to build that formula...

Thanks,
 
Upvote 0
You're welcome.

Regarding your follow up, can you post a set of data like you describe? Using a SUMIF or somekind of Array formula will work. But, I'd like to see the data layout.
 
Upvote 0
You're welcome.

Regarding your follow up, can you post a set of data like you describe? Using a SUMIF or somekind of Array formula will work. But, I'd like to see the data layout.
Cells A30 & A31 now are "Room Expenses" on "BUDGET 2024" so the formula does only pick the 1st one as same name appears right below so I would like to sum up both values together (228,836.54 + 20).


file.xlsx
ABCDEF
11
2
3
4Jan-24
5CurrentBudget
6
7
8
9
10
11
12Actual%Budget%
13228,837Room expenses
Numbers
Cell Formulas
RangeFormula
D13D13=INDEX('BUDGET 2024'!$B$8:$M$42,MATCH($F13,'BUDGET 2024'!$X$8:$X$42,0),MATCH($B$1,'BUDGET 2024'!$B$1:$M$1,0))
Cells with Data Validation
CellAllowCriteria
B4:E4List=$BP$1:$BP$12



file.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1123456789101112
2Statement of Income - Budget
3Rolling 12 Month Budcast
420/01/2024
5BudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastBudcastProjectionHideFilter
6Jan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24Nov-24Dec-24Totaldic 2023 T12dic 2022 T121
7Statement of Income
8Rooms Available202020202020202020202020240.001
9Rooms Sold202020202020202020202020240.001
10ADR202020202020202020202020240.001
11Occupancy%202020202020202020202020240.001
12RevPAR202020202020202020202020240.001
13Total RevPAR202020202020202020202020240.001
14
15REVENUE1
16
17Room Revenue202020202020202020202020240.001
18Food Revenue202020202020202020202020240.000
19Beverage Revenue202020202020202020202020240.00
20202020202020202020202020240.00
21Events Revenue202020202020202020202020240.000
22Parking Revenue202020202020202020202020240.000
23Spa Revenue202020202020202020202020240.00
24Miscellaneous Revenue202020202020202020202020240.00
25TOTAL REVENUE160.00160.00160.00160.00160.00160.00160.00160.00160.00160.00160.00160.001,920.001
26
27
28EXPENSES1
29
30Room Expenses228,836.54189,465.30316,821.67428,821.00524,121.00535,665.00444,665.00402,421.40522,421.00449,821.00242,621.00226,665.004,512,344.91Room Expenses
31Room Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.001Room Expenses
32Beverage Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000
3320.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.00
34Events Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000
35Parking Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000
36Spa Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.00
37Miscellaneous Expenses20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00240.000
38Departmental Expenses228,976.54189,605.30316,961.67428,961.00524,261.00535,805.00444,805.00402,561.40522,561.00449,961.00242,761.00226,805.004,514,024.911
39
40Administrative and General20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00513,068.921
41Info and Telecom Systems20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0029,403.101
42Sales and Marketing20.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.0020.00108,668.951
BUDGET 2024
Cell Formulas
RangeFormula
N8:N13,N30:N38,N17:N25N8=SUM(B8:M8)
B25:M25,B38:M38B25=SUM(B17:B24)
 
Upvote 0
OKay, the SUMIFS may work, but I already had the INDEX function working. So using a 0 for the row number argument returns the entire column. Then Matrix math against the row headers will get your values to use in a SUM function. (If you get an error you may need to use the CNTL-SHFT-ENTR - CSE keystroke).

Excel Formula:
=SUM(INDEX('Budget 2024'!$C$8:$M$42,0,
MATCH($B$1,'Budget 2024'!$B$1:$M$1,0))*
($F13='Budget 2024'!$A$8:$A$42))
 
Upvote 0
OKay, the SUMIFS may work, but I already had the INDEX function working. So using a 0 for the row number argument returns the entire column. Then Matrix math against the row headers will get your values to use in a SUM function. (If you get an error you may need to use the CNTL-SHFT-ENTR - CSE keystroke).

Excel Formula:
=SUM(INDEX('Budget 2024'!$C$8:$M$42,0,
MATCH($B$1,'Budget 2024'!$B$1:$M$1,0))*
($F13='Budget 2024'!$A$8:$A$42))
Fabulous!

All good now.

Extremely grateful to you!
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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