# Thread: lookup with multiple criteria

1. ## lookup with multiple criteria

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. In Addition, this report has to be in .xls format so I don't think I can use IFS function :-(

3. 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. 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. 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
•