Forum Discussion
Two way vertical and horizontal lookup
I want a formula to lookup a date, establish whats it falls onto using the week ending date, match that against the employee name and return the intersecting value.
For example, I want to see James' potential hours for 8/1/21, which should return 32 (B16). If the date was 04/04/21 then it should return 25.6 for James
The dates in my dataset are by week ending e.g. Sunday 10/1/21 so a date lookup would need to know that 8/1/21 falls under that week and returns the intersecting value against the employee name
I have tried the following xlookup to no avail
=XLOOKUP(B3,A13:A16,XLOOKUP(B4,B12:AW12,B16:AW16,"not found",1,1))
7 Replies
In principle, what you are doing is correct.
= XLOOKUP( name, employee, XLOOKUP(d,wkEnding,hours,"",1) )so the problem is in the detail. Maybe looking up a numeric datavalue against a text date.
=IFERROR(INDEX($B$13:$AW$16, XMATCH(B3, $A$13:$A$16), XMATCH(B4, $B$12:$AW$12, 1)), "not found")
- Thank you Hans, this seems to have done it. Why would did you use IFERROR and XMATCH over XLOOKUP? wouldn't the latter be far easier?
I'm expecting you'll see there are limitations to xlookup relevant to my dataset which would not make it work as i wasnt able to implement it so far. I confirm the dates are being handled as dates in excel and not as numbersThe third argument of your XLOOKUP formula should be an array, but it is XLOOKUP(B4,B12:AW12,B16:AW16,"not found",1,1) which returns a single value, not an array.
