+ Reply to Thread
Results 1 to 5 of 5

Formula comparing 2 tables

  1. #1
    Registered User
    Join Date
    12-16-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formula comparing 2 tables

    Capture.JPG

    Table is in attached picture.

    I need formula comparing these to two tables and the functioning as below

    A2 <= E3 and B2 = F1 then C2=F3

    A values will differ not exactly 2048 & 4096 it may be 2570

    i have tried this

    =INDEX($E$1:$M$6,IF(A5<=$E$1:$E$6,$E$1:$E$6),MATCH(B5,E$1:M$1,0))

    if should be looped until it is true..

    The value of A2(=2048) should be compared to E3 to E6. IFA2 is less or equal to E3 (A2 <= E3) then we will consider E3 row .

    Plz hlp! Thx in Advance
    Last edited by ksk.prasad; 12-16-2011 at 04:02 PM. Reason: UPDATE

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Formula comparing 2 tables

    Try this, C2 copy down

    =VLOOKUP(A2,$E:$M,MATCH(B2,E$1:M$1,0))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    12-16-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula comparing 2 tables

    It worked out, but if A2 values is 3456 it should check in value 4096 row, but it is taking 2048 row

    The Memory over head value for 3456 is 324.99

    Memory CPU Memory overhead
    2048 1 137.81
    4096 1 165.98
    4096 2 242.51
    3456 4 280.53
    4096 2 242.51
    4096 1 165.98
    Last edited by ksk.prasad; 12-16-2011 at 04:26 PM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Formula comparing 2 tables

    Try:

    =INDEX($E$4:$M$8,MATCH(TRUE,INDEX($E$4:$E$8>=A2,0),0),MATCH(B2,E$1:M$1,0))

    adjust ranges to suit.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Formula comparing 2 tables

    Try this,

    =HLOOKUP(B2,E$1:M$6,MATCH(A2,E$1:E$6)+(LOOKUP(A2,E$1:E$6)<A2),0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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