Extract Array Formula from data with multiple criteria

Question

I have the following table containing Laptop details

(Laptops)
Name                  |  RAM   |  CPU
===========================================
Laptop Example 1      | 4      | Intel® Core™ i3-8130U   
Laptop Example 2      | 8      | Intel® Core™ i5-8250U 
Laptop Example 3      | 16     | Intel® Core™ i5-8250U
Laptop Example 4      | 8      | Intel® Core™ i7-8550U
Laptop Example 5      | 8      | Intel® Core™ i5-8265U
...

I was looking to create an array using multiple criteria to simply return the name of the laptop. In this case I want to return the name of a laptop with 8 RAM & a CPU containing i5-.

In this table above I would expect to return

(Array)
Name                  
====================== 
Laptop Example 2       
Laptop Example 5      

I haven't had much experience with array formulas but I have been able to either return all laptop names with 8 RAM

=IFERROR(INDEX(Laptops!A$2:A$500,SMALL(IF(Laptops!$B$2:$B$500=8,ROW(Laptops!A$2:A$500)-ROW(Laptops!A$2)+1),ROWS(Laptops!A$2:A2))),"")

or CPU containing i5-

=IFERROR(INDEX(Laptops!A$2:A$500,SMALL(IF(IFERROR(SEARCH("i5-", Laptops!$C$2:$C$500), 0),ROW(Laptops!A$2:A$500)-ROW(Laptops!A$2)+1),ROWS(Laptops!A$2:A2))),"")

But unable to combine both using the AND function? I have tried...

=IFERROR(INDEX(Laptops!A$2:A$500,SMALL(IF(AND(Laptops!$B$2:$B$500="8",IFERROR(SEARCH("i5-", Laptops!$C$2:$C$500), 0),ROW(Laptops!A$2:A$500)-ROW(Laptops!A$2)+1),ROWS(Laptops!A$2:A2))),""))

However, it does not return anything. Any help would be greatly appreciated.

Answer 1

I would use a pivot table and then filter on RAM and apply a filter to the CPU column.

Some help in doing that here

Answer 2

My aproach to you question would be like this

1.- Get some auxiliar columns

Add a column "CPU Family"

=left(right(C2;8);2)

You can duplicate the Ram field if you which to see the ram when you filter

=B2&"gb"

2.- Insert Pivot Table

Select the whole range of data, CTRL+SHIFT+Space and go to Insert > Pivot Table

Here you would select Ram and CPU Family as your filters, On the Label Fields you would add Laptop Name and CPU

Translation: =LEFT(RIGHT(C2;8);2)

This is my approach without involving VBA

Cheers.