Xlookup multiple lookups

[ecrodrig]

I have a file that I want to do the following on xlookup C2 in T&MMthlyFile tab column B but I want it to only look at the row that has Total in it I am attaching a picture. So basically find Project1 but only give me row 2 since that is the row that has project 1 and TOTAL on it. Just need the first part of the formula as I nee dto add other information to it but need to know how to pull just that particular row.

Thanks in advance.

 

[Fluff]

How about using Filter

=FILTER(Sheet1!R2:R100,(Sheet1!A2:A100="Total")*(Sheet1!B2:B100=C2))

 

[ecrodrig]

I will try that and see if that works. Thanks

 

[Fluff]

Ok,

 

[ecrodrig]

Ok,

So that formula does work but the problem is that since I have multiple rows between Total it is giving me a Calc error. Any way to add something that skips non-Total rows? If you look at my image you will see that row A2 says Total but the next Total isn't until A5 which means when I drag the formula I get Calc error and even without dragging it I get Calc error.

 

[Fluff]

How about

=FILTER(Sheet1!R2:R100,(Sheet1!A2:A100="Total")*(Sheet1!B2:B100=C2),"")

 

[ecrodrig]

How about

=FILTER(Sheet1!R2:R100,(Sheet1!A2:A100="Total")*(Sheet1!B2:B100=C2),"")

This works, you are awesome!!! THANKS!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ecrodrig]

Sorry I did find one more issue. My "real" file where I need the output has 131 lines and the file where I am pulling the info only has 100 lines. When I pull down the formula I am good up to line 25 but after that, I get no more results. Any suggestions?

 

[Fluff]

Without being able to see both sheets, there's not much I can do.