Forum Discussion
VLOOKUP to the left
IF({1,0},G8:G19,E8:E19) is a sneaky way to return a range of two columns where the second column is to the left of the first column.
{1,0} is an array of two values. It causes IF to return two results, 1 is equivalent to TRUE, so this causes IF to return the value_if_true: G8:G19. 0 is equivalent to FALSE, so it causes IF to return the value_if_false: E8:19.
If you have Microsoft 365 or Office 2021, you can use
=XLOOKUP(F7,G18:G19,E8:E19)
In all versions, a non-sneaky but longer equivalent is
=INDEX(E8:E19,MATCH(F7,G8:G19,0))
OFFSET(D7,ROW(D7:D17)-ROW(D6),COLUMN(H7)-COLUMN(E7:G7)) evaluates to a single value, not to an array.
OFFSET(D7,ROW(D7:D17)-ROW(D6),COLUMN(H7)-COLUMN(E7:G7)) evaluates to a single value, not to an array.
When i pressed F9 on this part in the table_array part and pressed enter it evaluated correctly
Yes, but not in the final formula. Perhaps someone else can explain that.