Index/Match based on multiple criteria

Hello guys,

Hoping I can get yet some more assistance from you kind people.

I've attached a file which contains columns of numbers, names, 2 criteria columns, and a rate column. What I'm trying to do is to type a name into B19, and have (in the attached example) a value returned for that name, provided the criteria in columns C & D ("yes" & "full") are met.

I've made an attempt at an Index/Match formula that I though should work, but am getting a #Value error. In this example, the value returned should be E8 - "100" - as this is the cell which matches the name query in B19, and which has corresponding values of "yes" & "full".

Any help would be much appreciated.

Thank you,

Brendan.

One way

=LOOKUP(2,1/($B$2:$B$13=B19)/($C$2:$C$13="yes")/($D$2:$D$13="full"),$E$2:$E$13)

Try this array formula (confirmed with CTRL+SHIFT+ENTER):

=IF(B19="","",INDEX(E2:E13,MATCH(B19,IF((C2:C13="Yes")*(D2:D13="Full"),B2:B13,0),0)))

Gentlemen,

Thank you all very much for your help - these all work.

As a matter of interest - are there any advantages (or disadvantages - processing time, etc) to using an array formula, rather than one of the other two?

Brendan.

With a moderate amount of data I doubt there would be any noticable difference. Although 2 of the suggestions don't require array confirmation, there still operate on the same principles as an array formula would.

There are other criteria that would dictate the calculation time as much as if the formula is array confirmed or not, functions used, data range, etc.

The best way to test performance of a formula is to over-use it on a test worksheet with approx 10k rows of data and 1k formula, time the calculation with vba, then repeat the process with a different version of the formula and compare the results.

Fantastic. Thank you all for your input, and have a great weekend

Brendan.