Can DGET Return Multiple Matches?
Product Size Color Price Product Size Color Price
T-Shirt S Red $ 30.00 T-Shirt S Red Err:502 =DGET(B4:E16,J4,G4:I5)
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00 Product Size Price
T-Shirt XL Blue $ 24.00 Shirt XL Err:502 =DGET(B4:E16,I7,G7:H8)
Shirt S Olive $ 82.00
Shirt XL Blue $ 170.00 Product Price
Shirt XL Red $ 60.00 Hoodie Err:502 =DGET(B4:E16,H10,G10:G11)
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00
Hoodie S White $ 171.00
Hoodie M Green $ 101.00
Hoodie S Blue $ 71.00
Use of FILTER Function
Product Size Color Price Product Size Color Price
T-Shirt S Red $ 30.00 #NAME?
T-Shirt S Red $ 145.00 T-Shirt S Red #NAME?
T-Shirt S Red $ 22.00 #NAME?
T-Shirt XL Blue $ 24.00
Shirt S Olive $ 82.00 Product Size Price
Shirt XL Blue $ 170.00 #NAME?
Shirt XL
Shirt XL Red $ 60.00 #NAME?
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00 Product Price
Hoodie S White $ 171.00 #NAME?
Hoodie M Green $ 101.00 #NAME?
Hoodie
Hoodie S Blue $ 71.00 #NAME?
#NAME?
Practice Here!
Product Size Color Price Product Size Color Price
T-Shirt S Red $ 30.00
T-Shirt S Red $ 145.00 T-Shirt S Red
T-Shirt S Red $ 22.00
T-Shirt XL Blue $ 24.00
Shirt S Olive $ 82.00 Product Size Price
Shirt XL Blue $ 170.00
Shirt XL
Shirt XL Red $ 60.00
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00 Product Price
Hoodie S White $ 171.00
Hoodie M Green $ 101.00
Hoodie
Hoodie S Blue $ 71.00
Use of FILTER Function to Get All Matched Rows
Product Size Color Price Product Size Color
T-Shirt S Red $ 30.00 T-Shirt S Red
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00 Output
T-Shirt XL Blue $ 24.00
Shirt S Olive $ 82.00 #NAME? #NAME? #NAME? #NAME?
Shirt XL Blue $ 170.00 #NAME? #NAME? #NAME? #NAME?
Shirt XL Red $ 60.00 #NAME? #NAME? #NAME? #NAME?
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00
Hoodie S White $ 171.00
Hoodie M Green $ 101.00
Hoodie S Blue $ 71.00
Practice Here!
Product Size Color Price Product
T-Shirt S Red $ 30.00 T-Shirt
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00 Output
T-Shirt XL Blue $ 24.00
Shirt S Olive $ 82.00
Shirt XL Blue $ 170.00
Shirt XL Red $ 60.00
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00
Hoodie S White $ 171.00
Hoodie M Green $ 101.00
Hoodie S Blue $ 71.00
re!
Size Color
S Red
Output
Use of TEXTJOIN Function to Get Multiple Records in One Cell
Product Size Color Price Product Size Color Prices
T-Shirt S Red $ 30.00 Shirt XL Blue $170
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00 Product Size Prices
T-Shirt XL Blue $ 24.00 Shirt XL $170 , $60
Shirt S Olive $ 82.00
Shirt XL Blue $ 170.00 Product Prices
Shirt XL Red $ 60.00 Hoodie $139 , $171 , $101 , $71
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00
Hoodie S White $ 171.00
Hoodie M Green $ 101.00
Hoodie S Blue $ 71.00
Practice Here!
Product Size Color Price Product Size Color Prices
T-Shirt S Red $ 30.00 Shirt XL Blue
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00 Product Size Prices
T-Shirt XL Blue $ 24.00 Shirt XL
Shirt S Olive $ 82.00
Shirt XL Blue $ 170.00 Product Prices
Shirt XL Red $ 60.00 Hoodie
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00
Hoodie S White $ 171.00
Hoodie M Green $ 101.00
Hoodie S Blue $ 71.00
Use of INDEX Formula
Product Size Color Price
T-Shirt S Red $ 30.00
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00
T-Shirt XL Blue $ 24.00
Shirt S Olive $ 82.00
Shirt XL Blue $ 170.00
Shirt XL Red $ 60.00
Shirt XXL Green $ 152.00
Hoodie L Marron $ 139.00
Hoodie S White $ 171.00
Hoodie M Green $ 101.00
Hoodie S Blue $ 71.00
ormula
Product Price Product Size
$ 139.00 T-Shirt S
$ 171.00 T-Shirt S
Hoodie
$ 101.00 T-Shirt S
$ 71.00 T-Shirt XL
Shirt S
Shirt XL
Shirt XL
Shirt XXL
Hoodie L
Hoodie S
Hoodie M
Hoodie S
Practice Here!
Color Price Product Price
Red $ 30.00
Red $ 145.00
Hoodie
Red $ 22.00
Blue $ 24.00
Olive $ 82.00
Blue $ 170.00
Red $ 60.00
Green $ 152.00
Marron $ 139.00
White $ 171.00
Green $ 101.00
Blue $ 71.00
Formula Explanation
Product Size Color Price Product
T-Shirt S Red $ 30.00 Hoodie
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00 Formula: =IFERROR(INDEX($E$5:$E
T-Shirt XL Blue $ 24.00
Shirt S Olive $ 82.00 SL of Products
=ROW($B$5:$B$16)
Shirt XL Blue $ 170.00 -ROW($B$5)+1
Shirt XL Red $ 60.00 1
Shirt XXL Green $ 152.00 2
Hoodie L Marron $ 139.00 3
Hoodie S White $ 171.00 4
Hoodie M Green $ 101.00 5
Hoodie S Blue $ 71.00 6
7
8
9
10
11
12
mula Explanation
ormula: =IFERROR(INDEX($E$5:$E$16,SMALL(IF($G$5=$B$5:$B$16, ROW($B$5:$B$16)-ROW($B$5)+1), ROW(1:1))),"" )
SL of Prodcuts That First Matching INDEX_array Output (9th Item)
Match Criteria Prodcut's SL
=IF($G$5=$B$5:$B$16, =SMALL(IF($G$5=$B$5:$ =IFERROR(INDEX($E$5:$E$16,SMALL
ROW($B$5:$B$16)- B$16, ROW($B$5:$B$16)- =$E$5:$E$16 (IF($G$5=$B$5:$B$16,
ROW($B$5)+1), ROW($B$5:$B$16)-ROW($B$5)+1),
ROW($B$5)+1)
ROW(1:1)) ROW(1:1))),"" )
0 9 $ 30.00 $ 139.00
0 $ 145.00
0 $ 22.00
0 $ 24.00
0 $ 82.00
0 $ 170.00
0 $ 60.00
0 $ 152.00
9 $ 139.00
10 $ 171.00
11 $ 101.00
12 $ 71.00
Use of INDEX Formula
Product Size Color Price Product Size Price Product
T-Shirt S Red $ 30.00 $ 170.00 T-Shirt
Shirt XL
T-Shirt S Red $ 145.00 $ 60.00 T-Shirt
T-Shirt S Red $ 22.00 T-Shirt
T-Shirt XL Blue $ 24.00 T-Shirt
Shirt S Olive $ 82.00 Shirt
Shirt XL Blue $ 170.00 Product Size Color Price Shirt
Shirt XL Red $ 60.00 $ 30.00 Shirt
Shirt XXL Green $ 152.00 T-Shirt S Red $ 145.00 Shirt
Hoodie L Marron $ 139.00 $ 22.00 Hoodie
Hoodie S White $ 171.00 =IFERROR(INDEX($E$5:$E$16, Hoodie
SMALL(IF(COUNTIF($G$11,
Hoodie M Green $ 101.00 $B$5:$B$16)*COUNTIF($H$11, Hoodie
$C$5:$C$16)*COUNTIF($I$11,
$D$5:$D$16), ROW($B$5:$B$16)-
Hoodie S Blue $ 71.00 MIN(ROW($B$5:$B$16))+1), ROW(A1)), Hoodie
COLUMN(A1)),"")
Practice Here!
Size Color Price Product Size Price
S Red $ 30.00
Shirt XL
S Red $ 145.00
S Red $ 22.00
XL Blue $ 24.00
S Olive $ 82.00
XL Blue $ 170.00 Product Size Color Price
XL Red $ 60.00
XXL Green $ 152.00 T-Shirt S Red
L Marron $ 139.00
S White $ 171.00
M Green $ 101.00
S Blue $ 71.00
Use of Advanced Filter Feature
Product Size Color Price Product
T-Shirt S Red $ 30.00 Hoodie
T-Shirt S Red $ 145.00
T-Shirt S Red $ 22.00
T-Shirt XL Blue $ 24.00 All the Records That Match These Criteria
Shirt S Olive $ 82.00
Shirt XL Blue $ 170.00 Product Size Color Price
Shirt XL Red $ 60.00 Hoodie L Marron $ 139.00
Shirt XXL Green $ 152.00 Hoodie S White $ 171.00
Hoodie L Marron $ 139.00 Hoodie M Green $ 101.00
Hoodie S White $ 171.00 Hoodie S Blue $ 71.00
Hoodie M Green $ 101.00
Hoodie S Blue $ 71.00
Practice Here!
Product << Set a criteria
Hoodie
