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 s...
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
  • 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

      • MirDia1985's avatar
        MirDia1985
        Copper Contributor
        Sep 13, 2021
        Hi, thanks a lot for ur cooperation.
        Is it possible to create a formula which uses VLOOKUP from differnt excel file?
        Thank you

Resources