Forum Discussion

MirDia1985's avatar
MirDia1985
Copper Contributor
Sep 11, 2021
Solved

formula needed for VLOOKUP

Good evening, I am looking for an VLOOPUP formula or other formula, which check two base (BER and LEJ) from table in whole table on various columns and the dedicated price for the base from another sheet and should automatic fill in the correct figure in the fieled park in sheet "base". 
I have attached the excel table. Thank you in advance

excel
Formulas and Functions
office 365
  • MirDia1985's avatar
    MirDia1985
    Sep 15, 2021
    Hi, is it possible to lookup for price only in column C, but to search in columns A and C, but exclude column B. Thx

23 Replies

    • MirDia1985's avatar
      MirDia1985
      Copper Contributor
      Sep 12, 2021

      HansVogelaar 

      Hi, thank you. I have tried for another table, see attachment. 
      The formula should be from FL program and Expenses per Week.
      In the column Parking should look up from Column "D" if DUS or LEJ and check price from sheet "Expenses per Week" from A3 to B4, Like if DUS than 888 and if LEJ than 999 should automatically be display at the column in "Parking" in sheet FL program. 

      Looking forward

      P.S. Can then i just copy free to another tables and what should i take attention to.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 12, 2021

        MirDia1985 

        There are two problems:

        1. Cell A4 on the EXPENSES sheet contains a space after DUS: "DUS " instead of "DUS". This prevents the VLOOKUP formula from finding a match for "DUS". Remove that space.
        2. There are spaces at the end of the name of the EXPENSES sheet: it is "EXPENSES_PER_WEEK   " instead of "EXPENSES_PER_WEEK". Remove those spaces from the name of the sheet and from the VLOOKUP formulas.

        Also, I'd use $A$3:$B$4 as lookup range. That way, you can copy the formula up or down and the range will stay the same.

        After making the changes in 1. and 2. above, Select AH7:AH53 on FL_Program.

        Enter the formula

         

        =IFERROR(VLOOKUP(D7,EXPENSES_PER_WEEK!$A$3:$B$4,2,FALSE),"")

         

        In German:

         

        =WENNFEHLER(SVERWEIS(D7;EXPENSES_PER_WEEK!$A$3:$B$4;2;FALSCH);"")

         

        and press Ctrl+Enter to populate all selected cells. Excel will automatically adjust D7 to D8 ... D53 in the rows below, while keeping $A$3:$B$4 the same.

         

        Another problem: the formulas in column S perform a lookup of the currency in column R in R1:S2. But R1 and R2 contain 111 and 110, not USD or similar. I don't know what you intended there.

         

        See the attached version.

         

        P.S. M1 contained a #REF! error so I entered 1,18

Resources