 # 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 7 Replies

# Re: Two way vertical and horizontal lookup

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.

# Re: Two way vertical and horizontal lookup

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 numbers

# Re: Two way vertical and horizontal lookup

The 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.

# Re: Two way vertical and horizontal lookup

Well spotted.  I haven't used a direct cell reference for so long that I tend to see them but not register their content.  I simply replaced the reference by a two dimensional array 'hours' and, of course, the formula worked perfectly.

# Re: Two way vertical and horizontal lookup

Hi both, struggling to see the error in the formula, the third argument is referencing a return array B16:AW16 so what am I doing wrong?

# Re: Two way vertical and horizontal lookup

Your original formula has two XLOOKUPS: