Spreadsheets

mira2602
mira2602 Registered Posts: 29 Regular contributor ⭐
Hello there, just wanted to ask for help with formulas-vlookup, if...kind of doesn't seems to sink in at all. Any advice? Maybe I am looking at them from wrong angle.

Many thanks in advance

Comments

  • janwal
    janwal Registered Posts: 1,189 Beyond epic contributor 🧙‍♂️
    Don't know if this will help, but I find U tube very handy

    http://www.youtube.com/watch?v=KXhM-wBM7U4

    Jan
  • Louise89
    Louise89 Registered Posts: 296 Dedicated contributor 🦉
    When you type in a lookup if you look below the cell that you are typing into it tells you what you need to put in.

    For example;

    =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

    Lookup_value = the cell you are referencing

    Table_array = the table in which you are trying to find the referenced value

    Col_index_num = the column you are trying to get values from

    Range_lookup = True - if you want an approx match (be careful with this one, you probably wont even use it)
    = False - an exact match


    And remember when pulling down the formula to F4 the table array otherwise it probably wont work.

    I've got my SPSW exam tomorrow, so I know how you're feeling... GOOD LUCK! :)
  • mrb82
    mrb82 Registered Posts: 147 Beyond epic contributor 🧙‍♂️
    I talk myself through the vlookup like this (this is all inside the brackets, btw)

    Look for this cell, against this range of cells, bring back the result from column 10 (so for J you put 10), bring back false if it doesn't match

    so...

    =vlookup(A1,[workbook]sheet1!A:J,10,false)

    where I've put [workbook]sheet1! it just means i've clicked the range of cells I want to search against in another workbook. It should automatically do this for you, you just click what you want.

    The value you are searching for (A1) will need to also be the start of the cells you're searching against (so A to J, A1 will need to match the data in A), you're then specifying which column you want it to bring back the information for (So for A:J, you're saying if A matches A1, then bring back the information from cell J).

    Oh, and if you're not selecting whole columns, just a range of cells (say A1 to C10), then press F4 to "lock" the data selection, else when you copy and paste the formula, it will shift the selection along (A1:C10 then becomes A2:C11 then A3:C12 and so on).

    Does this make sense?
  • anisurrahman
    anisurrahman Registered Posts: 130 Beyond epic contributor 🧙‍♂️
    Hi Mira

    If you need any help with Excel formula.. just give me a shout..

    Ronnie
  • mira2602
    mira2602 Registered Posts: 29 Regular contributor ⭐
    Guys, thank you very much for yours replies. Can I ask what happend when you combined IF FORMULA with VLOOKUP, for example?

    I might seem stupd, but what does '''','''', in formula means?
    When doing revision, I came across the '''' sign within the answer.
    Many thanks in advance. Well appreciated.
  • anisurrahman
    anisurrahman Registered Posts: 130 Beyond epic contributor 🧙‍♂️
    Hi Mira

    IF formula with VLookup function can be used to return exact values if found, and an empty string if not found.


    Example: Start the VLookup formula with an IF formula using the ISNA function to check for an #N/A error:


    =IF(ISNA(VLOOKUP(G12,D12:E18,2,FALSE)),"",VLOOKUP(G12,D12:E18,2,FALSE))
    


    Double quotes " " use in IF statement formula for text value

    For example:
    =IF(A1=20,"This is Text 1","This is Text 2")
    


    If yo use number or numerical value as your IF statement True or False value then you don't have to use " "
    For example:
    =IF(A8="Apple",20,0)
    

    For more IF functions please click on this link: http://www.cpearson.com/excel/nested.htm

    Cheers

    Ronnie
  • mira2602
    mira2602 Registered Posts: 29 Regular contributor ⭐
    Hi Ronnie, thank you for your reply, sorry did not have time as had some family emergency and even had to postpone my simulation. But here I am, back to studies. Thanks again.
Privacy Policy