Forum Discussion

hussain_786_23's avatar
hussain_786_23
Copper Contributor
Oct 12, 2021

VLOOKUP to the left

Hi,

 

I have windows 10 and Excel 2016

 

I came across this formula on for VLOOKUP to the left but didn’t understand it

VLOOKUP(F7,IF({1,0},D:D,B:B),2,0)
 

In both these cases the destination column is the second one in braces and 2 is the column index number

Can you help me understand how it works?
 
I know this is similar to using the CHOOSE function except that in the former {1,0} might refer to TRUE and FALSE but how can the IF function be without logical_test?
 
CHOOSE probably is more flexible than the above formula
 

Of course i understand that INDEX / MATCH and XLOOKUP is a better option but i just wanted to understand the Concept of why IF function works without a true and false entry in this case.

My intention is to clear any misconceptions and learn new concepts since you are extremely good at breaking down the formula and explaining it

 

Also on similar lines i tried using the offset function to generate the same array in the table field in the VLOOKUP function (details in the attachment)

 
=+VLOOKUP(A8,OFFSET(D7,ROW(D7:D17)-ROW(D6),COLUMN(H7)-COLUMN(E7:G7)),3,0)
 
it gives the #REF! error but when I press F9 on the OFFSET(D7,ROW(D7:D17)-ROW(D6),COLUMN(H7)-COLUMN(E7:G7)) part and then press enter it evaluates correctly
 
I don't understand why
 
It would be great if you could explain
 
Regards,
Hussain
Management Accountant and excel noob
Formulas and Functions

5 Replies

  • gufygozali's avatar
    gufygozali
    Copper Contributor
    Oct 12, 2021

    hussain_786_23 

    this is an array formula inside logical_test IF, 

    {1,0} or {1\0} represent {TRUE,FALSE} or {TRUE\FALSE}

    since logical_test {1,0} is an array, formula IF will excecute 1 (or TRUE) and 0 (or FALSE), it's mean D:D and E:E will show, and it will create table_array D:E for VLOOKUP

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 12, 2021

    hussain_786_23 

    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.

    • hussain_786_23's avatar
      hussain_786_23
      Copper Contributor
      Oct 12, 2021

      HansVogelaar 

      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

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Oct 12, 2021

    hussain_786_23 

     

    VLOOKUP(F7,IF({1,0},D:D,B:B),2,0)
    F7=lookup value
    IF({LOOKUPCELL IS TRUE(1),0},THEN LOOKUP RANGE=D:D, RETURN RANGE=B:B ELSE IF IT IS BLANK RETURN A ZERO
    range D:D = lookup range
    range B:B = return value range
    2= can only be a value of 2 or 1
    0= exact match

     

     

Resources