Results 1 to 5 of 5

Thread: lookup with multiple criteria

  1. #1

    Question lookup with multiple criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Newbie to this forum, so apologies if this has been answered somewhere else. I couldn't find something similar

    I have 2 tables of data, in the column 'Type RAM CPU Calc (left table), I need the formula to look to see if the category is 'Virtual Hardware' or 'Hardware', how much RAM there is, multiply the Cores and CPU's to get total CPU and work out which Type (A-H) from the right hand table always basing on highest Value (either CPU or RAM), is there one formula that can do this? Many thanks

    Category RAM Cores CPU's Type RAM CPU Calc Category Type CPU RAM
    Virtual Hardware 4096 1 1 Virtual Hardware A 1 2048
    Virtual Hardware 1024 2 2 Virtual Hardware B 2 4096
    Virtual Hardware 2048 1 3 Virtual Hardware C 4 8192
    Virtual Hardware 4096 1 4 Virtual Hardware D 6 16384
    Hardware 4 1 Virtual Hardware E 8 32768
    Hardware 4096 1 2 Virtual Hardware F 12 49152
    Hardware 4096 2 1 Virtual Hardware G 24 65536
    Virtual Hardware H 32 98304
    Hardware A 1 4096
    Hardware B 2 8192
    Hardware C 4 16384
    Hardware D 6 16384
    Hardware E 8 32768
    Hardware F 12 49152
    Hardware G 24 65536
    Hardware H 32 98304

  2. #2
    In Addition, this report has to be in .xls format so I don't think I can use IFS function :-(

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,821
    Articles
    0
    Excel Version
    O365
    Try this array formula

    =IFERROR(INDEX($R$2:$R$8,MATCH(1,($O$2:$O$8=A2)*($Q$2:$Q$8=D2),0)),"no match")

  4. #4
    Hi Bob, thank you for this... my brain is fried from looking at this all day, please could you tell me in your formula, which columns R, O, Q refer to so i can amend accordingly in my spreadsheet.... Feeling the pain

  5. #5
    Thanks Bob, I have the formula in my SS and it is returning results, unfortunately though it is only returning if there is a complete match and also is not taking in to account the highest value, i.e. If value of RAM = Type E and value of CPU's = type D or vise versa, then the result should be Type E (whatever the highest value is). Also it should be looking at <= to the values as they may not be exact...... The previous formula used for this was =IF(OR(C2=0,(D2*E2)=0),"U",IF(AND(C2<=2048,(D2*E2)<=1),"A",IF(AND(C2<=4096,(D2*E2)<=2),"B",IF(AND(C2<=8192,(D2*E2)<=4),"C",IF(AND(C2<=12288,(D2*E2)<=6),"D","D+"))))) however this only accounted for types A-D and D+ not A-H (I think this would now be too many IF statements) and didn't account for Hardware or Virtual Hardware which now have different criteria and so is another consideration in the formula. Tired now

Posting Permissions

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