What is Numbers equivalent of this Excel formula?
I am unable to figure out the equivalent Numbers formula for this: https://www.youtube.com/watch?v=TVD1GJrDsKk. I tried an Index with nested match functions but could not make it work.
Question marked as
Hi Sue,
I'm not sure why the person doing the tutorial is choosing an array formula for this, With the data provided on his table, a simpler lookup, using MATCH and INDEX works fine.
I've reversed the location of the Main and lookup tables, as I usually place the table receiving entered data (Main) on the left.
As can be seen from the highlighting on the lookup table, column B is redundant—The April pay period begins on March 28, and ends the day before the next pay period starts.
What happens at the junction between two pay periods is more significant that what happens with a date in the 'middle' of a pay period, hence the list of consecutive dates in rows 5 to 9 of Main, showing the transition from April to May pay periods occurs at the correct point in time.
The formula shown is entered in Main::B2, then filled down to B10 (with some editing in the last cell). The error triangle in B4 results because ' MATCH couldn't find the value "" ' from that row of column A.
To avoid that error flag, I've wrapped the core formula shown in an IF statement that prevents the calculation IF the bell in column A is empty.
B10: IF(LEN(A10)<1,"",INDEX(lookup::C,MATCH(A9,lookup::A,1)))
BTW, VLOOKUP, which the 'tutor' rejects immediately, could also be used here:
Wrap it in the same switch as above to avoid the error triangle.
Regards,
Barry