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

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

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.

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

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 & 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.

Suggest you read here on Oz about both.