I've provided an example to better explain my problem
The yellow highlight is where you input the data needed. The problem is it doesn't show the result when the letter has a duplicate. For example, when I enter "a" in the first input cell(input letter) and "1" in the next(input number), I get the result("a1"). However when I input "a" in the first and "2" in the second, the result just shows a blank cell, and so forth. How do I solve this?
Lookup With 2 Criteria & Duplicates Exist



Re: Nth Criteria Error In Vlookup
Change the formula in E3 to
[COLOR="Blue"]=INDEX(C2:C10, MATCH(E2&E3, A2:A10 & B2:B10, 0))[/COLOR]
... and then replace the hardcoded range references with dynamic named ranges.

Re: Nth Criteria Error In Vlookup
err... sorry but I'm not familiar with dynamic named ranges. I even looked at the tutorial and still didn't get it. Can you explain it? better yet maybe you can just implement it to my sample?

Re: Nth Criteria Error In Vlookup
There are a huge number of examples on Oz, in addition to the tutorial. How about you get started, and then come back with a specific question?

Re: Nth Criteria Error In Vlookup
shg's suggested formula needs to be confirmed with CTRL+SHIFT+ENTER.
here's another way that requires just ENTER
=LOOKUP(2,1/((A3:A10=E2)*(B3:B10=E3)),C3:C10)
or
=INDEX(C3:C10,MATCH(1,INDEX((A3:A10=E2)*(B3:B10=E3),0),0))
These will give the same answers for your example but if there was more than one match for any combination the LOOKUP formula will find the last, the INDEX/MATCH formula will find the first.....


Re: Nth Criteria Error In Vlookup
Quoteshg's suggested formula needs to be confirmed with CTRL+SHIFT+ENTER.
Indeed it does, DLL, thank you  but I like your solutions better, and recommend them to the OP. 
Re: Nth Criteria Error In Vlookup
I really like all solutions that Shg and DLL are contributing. I am also learning al ot from them.
Thank you guys.
Biz

Re: Lookup With 2 Criteria & Duplicates Exist
Quoteshg's suggested formula needs to be confirmed with CTRL+SHIFT+ENTER.
Is that what he meant by dynamic named range? I thought it has something to do with the "offset" formula. Well I know that CTRL+SHIFT+ENTER thing, just didn't know that it was called dynamic name range, I feel stupid.:smile: 
Re: Lookup With 2 Criteria & Duplicates Exist
Quote from miko68Is that what he meant by dynamic named range? I thought it has something to do with the "offset" formula. Well I know that CTRL+SHIFT+ENTER thing, just didn't know that it was called dynamic name range, I feel stupid.:smile:
CTRL + SHIFT + ENTER makes a formula an array formula. When you look in the formula bar in Excel you would see your formula surrounded in brackets { }.
I would read http://www.ozgrid.com/Excel/DynamicRanges.htm to get a good understanding of the Dynamic Named Range concept.

Re: Lookup With 2 Criteria & Duplicates Exist
Time out, miko: array formulas and dynamic range names are completely different things. Range names (dynamic or static) can be used in formulas (regular or array).
Entering a formula with CSE instead of just Enter causes the formula to be entered as an array formula, and it appears within curly braces as confirmation. They are used when formulas need to evaluate values across a range. (Some functions require arrays, and do not need array formulas to do this.)
A named range is a way to assign a name to an array. So you can define, for example,
[COLOR="Blue"]Bob: =$A$3:$A$10[/COLOR] is a static named range. (Sort of static, anyway; if you insert a row between A3 and A10, the definition will automatically change.) Then =SUM(Bob) is the same as =Sum($A$3:$A$10). Used well, they facilitate understanding spreadsheet design.
Defining a range name such that its extent is variable based on some function (usually COUNTA or MATCH) makes the range dynamic, which makes where it begins or ends (or both), variable according to data in the spreadsheet. So:
[COLOR="blue"]Bob: =OFFSET($A$2, 1, 0, COUNTA($A$2:$A$65536)1, 1)[/COLOR] is a dynamic named range.
Suggest you read here on Oz about both.


Re: Lookup With 2 Criteria & Duplicates Exist
I see, guess I have much to learn if I don't even know what dynamic range is. Anyway, the formula you've given already worked when I entered it as an array formula. Thanks.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!