Supposing every product (KTE, KTO, KTW) has three stores (A, B, C), and every store imported items twice, now you want to return the number of items of product KTO in store B at the first time as below screenshot shown. How can you quickly find the value in the given range in Excel? In this tutorial, it will provide an array formula to solve this job.
As the above screenshot shown, you need to list three criteria you use to look up data, then type this array formula
in a blank cell which will return the value you want.
$A$3:$D$11: the whole range of data.
G1&G2: criteria1 and criteria2.
$A$3:$A$11&$B$3:$B$11: two ranges that criteria1 and criterai2 are in.
G3,$A$2:$D$2: criteria 3, and the range that criteria 3 is in.
Then press Shift + Ctrl + Enter to return the correct result.
The formula is case insensitive.
Tip: If above formula is too complex to remember for you, you can save it in to the Auto Textpane of Kutools for Excel, then you can use this formula in anywhere anytime without rote memorization and searching in internet again, only you need to do is click to apply it and change the reference as you need. Click for free download it now.
VLOOKUP and return default value In Excel, it will return the error value #N/A if it does not find the matched value while using VLOOKUP function. For avoiding the error value appears, you can use a default value to replace the error value if not found the matched one.