» Retrieving an Item from a List that Meets Multiple Criteria
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
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
Book Store:
Retrieving an Item from a List that Meets Multiple Criteria
Ola
I need to convert the index/match functions to WorksheetFunction.Index and WorksheetFunction.Match so that I can use a separate workbook and use ranges in vba
Any ideas
If someone is more confortable using vlookup than index and match function, concatenate the file and type string in source table and into the destination table and then vlookup for concatenated string in the destination table.