# Lookup With 2 Criteria & Duplicates Exist

• 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?

## Files

• 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 hard-coded range references with dynamic named ranges.

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• 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?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• 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

Quote

shg'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.

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• 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 &amp; Duplicates Exist

Quote

shg'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 &amp; Duplicates Exist

Quote from miko68

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:

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 &amp; 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 C-S-E 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.