Results 1 to 4 of 4

Thread: formula help

  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula help

    Hello

    I would be very grateful if anyone could shed some light on where i am erring with the formula below
    =IF(A2=1,G2,vlookup,Sheet2!C10:d12C1012,Sheet2!F10:G12))
    Can somebody please help with the above formula. I am trying to get the program to look and see if the value in a2=1 or a2=2 then lookup the value of g2 using either one of two look up tables all depending if a2=1 or a2=2.

    Much Thanks for any advice
    Graham

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula help

    Hi Graham,

    If I am reading you right the following formula should do it :

    =IF(A2=1,VLOOKUP(G2,Sheet2!C1012,2,FALSE),IF(A2=2,VLOOKUP(G2,Sheet2!F10:G12,2,FA LSE),0))

    If Cell A2 will always be either 1 or 2, you could change it to :

    =IF(A2=1,VLOOKUP(G2,Sheet2!C1012,2,FALSE),VLOOKUP(G2,Sheet2!F10:G12,2,FALSE))

    If you are looking for the nearest value to G2 instead of an exact match, you can omit the ,FALSE in all the above. I am assuming you are looking for the value in the second column of each lookup range.

    A good idea might be to name those ranges say Table1 and Table2, so instead of entering C1012 you would enter Table1 in the above formulas.

    The Vlookup formula works as follows :

    VLOOKUP(what you want to look for, the range where you want to look, and the column of that area you want the value from, and then if you want an exact match you enter ,FALSE).
    The value you are looking for (G2) must be in the first (leftmost) column of the area you are looking up (C10:G12). If you look for an exact match and it cannot be found, you will get #N/A error.

    Hope I understood your problem and you can follow the above.

    Regards,

    Andrew C

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Cleveland, Ohio, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help

    comic]]<font color=blue>The problem is that you have a comma after vlookup instead of enclosing it all in parentheses. It should be:

    =IF(A2=1,VLOOKUP(G2,SHEET2!C1012,2), VLOOKUP(G2, SHEET2!F10:G12,2))

    I would also suggest naming your lookup ranges. This will make the function much easier to work with. My above function says: "If A1=1 the go to Sheet 2 to the lookup table contained in cells C1012 and look for the corresponding information in the second column. If A1 does not equal 1 then go to Sheet 2 to the look up table contained in Cells F10:G12 and look for the corresponding information in the second column. I hope this is helpful!</font face=comic>]</font color=blue>

  4. #4
    New Lounger
    Join Date
    Mar 2001
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help

    Hi Guys
    Thanks very much for the assistance once again. i am very grateful for the assistance and knowledge I am recieving from this site.

    Graham

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •