lookup

[jtwork]

i am familiar with the sumproduct function which allows me to lookup/sum a column based on various criteria's

i want to be able to lookup a text value based on multiple conditions.

Here is the layout

columna = date
columnb = ref (not unique - number between 8 and 20)
columnc = id (either 2 or 3)
columnd is a name (text)

i want to lookup the name based on criteria for columns a to b

there is only one entry for the criteria i want to request

i have looked at vlookup but this is based on one condition

[NBVC]

Try

=Index(D2:D100,Match(1,(A2:A100=criteria1)*(B2:B100=criteria2),0))

adjust ranges to suit and insert criteria as necessary.

Formula must then be confirmed with CTRL+SHIFT+ENTER not just ENTER

[daddylonglegs]

You could also try a formula like this which doesn't require CTRL+SHIFT+ENTER

=LOOKUP(2,1/((B2:B100=20)*(C2:C100=3)),D2:D100)