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.

 

[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?

I found the issue everything needed to stay constant so $R$2:$R$100 and so on. This has fixed my problem. THANKS again

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[ecrodrig]

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

I fixed it just needed to make all of them constant as in $R2:$R100

 

[CluaseTony]

You could try this and see if it leads to another way to achieve.
small data set and the XLOOKUP formula in D11 is =XLOOKUP("Total"&C11,$A$2:$A$8&$B$2:$B$8,$D$2:$D$8)
you join the lookup together and also join the lookup array together so they match, then target the column you want the answer from.
columns in use are A to D, result are underneath in column D (June dollars)

PM Project	RFS #		June dollars
Total	Project1		100​
1​	Project1
2​	Project1
Total	Project2		200​
	Project2
Total	Project3		500​
4​	Project3
		Project1	100​
		Project2	200​
		Project3	500​
		Project4	#N/A​

 

[Fluff]

Why would you want to use that when the filter function is far more efficient?

the XLOOKUP formula in D11 is

And which is D11? Why not use the XL2BB add-in to post data in future, that way we can see which cell is which.

 

[CluaseTony]

Hi Fluff, First time in using this not sure how to upload my example Excel file, just wanted to show another way to get those answers.