Tip Printed from ExcelTip.com

---

Retrieving an Item from a List that Meets Multiple Criteria


Problem:

Range1 (A3:C7) contains file names with their matching types and sizes.
Range2 (A12:B16) contains a similar list of file names but with matching types only.
We want to find the appropriate file sizes from Range1 by matching each pair of file names and types from both ranges.

Solution:

Use the INDEX and MATCH functions as shown in the following formula:
{=INDEX($C$3:$C$7,MATCH(A12&B12,$A$3:$A$7&$B$3:$B$7,0))}

(To create Array formula: select the cell after typing the formula,press F2 and then press Ctrl+Shift+Enter)

Example:

Range1

File Name_______File Type_______Size (KB)
file1___________xls_____________500
file2___________doc_____________80
file3___________ppt_____________800
file4___________xls_____________400
file1___________ppt_____________1200


Range2

File Name_____________File Type_____________Size (KB) from Range1
file1_________________xls___________________500
file2_________________doc___________________80
file3_________________ppt___________________800
file4_________________xls___________________400
file1_________________ppt___________________1200