+ Reply to Thread
Results 1 to 5 of 5

Divide two vlookups

  1. #1
    Registered User
    Join Date
    07-28-2014
    Location
    UK
    MS-Off Ver
    2007
    Posts
    4

    Angry Divide two vlookups

    Hi All,

    I am using the below formulas in one of my sheets:

    G8 contains
    =IF(ISNA(VLOOKUP(A8,'[4. GC & 5. DFS Volumes 0614.xlsx]4. GC'!$A$5:$H$660,8,FALSE)),"",VLOOKUP(A8,'[4. GC & 5. DFS Volumes 0614.xlsx]4. GC'!$A$5:$H$660,8,FALSE))

    H8 contains
    =IF(ISNA(VLOOKUP(A8,Data!$A$4:$I$2950,8,FALSE)),"",VLOOKUP(A8,Data!$A$4:$I$2950,8,FALSE))

    i have tons of data in the sheet picked up from differnet workbooks/sheets etc.
    some fields in column g and h have returned a blank cell.
    i am trying to divide H8 by G8 but i keep getting #VALUE where there are no values in both or one of the cells in column g or h.
    is there a way to get around this by either changing columns g or h or changing my divide formula which is just =H8/G8?
    thanks

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,061

    Re: Divide two vlookups

    Maybe
    =IF(OR(G8="",H8="",0,H8/G8))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Divide two vlookups

    Or
    =IF(OR(G8="",H8=""),0,H8/G8)

    Or
    =IFERROR(H8/G8,0)

  4. #4
    Registered User
    Join Date
    07-28-2014
    Location
    UK
    MS-Off Ver
    2007
    Posts
    4

    Re: Divide two vlookups

    thanks, just tried that and it says there is an error with the formula.
    my formula works fine where the vlookup ups in G and H have values but anywhere it has retuned a blank i get #VALUE
    so annoying, why cant it just take the blanks as 0 when dividing?
    any other ideas? thanks again....

  5. #5
    Registered User
    Join Date
    07-28-2014
    Location
    UK
    MS-Off Ver
    2007
    Posts
    4

    Re: Divide two vlookups

    ahhh just realised that the above works i just had an extra bracket in by accident.
    Thanks for your help guys, the above formulas work perfectly.
    Legends

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vlookups
    By Dibbley247 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2013, 07:52 AM
  2. [SOLVED] Vlookups
    By chris.slater in forum Excel General
    Replies: 1
    Last Post: 03-15-2012, 02:34 PM
  3. Vlookups
    By BSDScotty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-19-2009, 06:23 AM
  4. vlookups
    By Mike0GW in forum Excel General
    Replies: 6
    Last Post: 12-17-2008, 09:09 PM
  5. If's against Vlookups
    By Paul Morgan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-19-2008, 04:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1