Change Vlookup to lookup against 2 criterias

One way

Create a combined column on both sheet1 and sheet2 concatenating the values from columns A & H
In row2 and copy down

Formula:

Please Login or Register  to view this content.

Use the new columns for the match

You need to change the formula in K2 of Sheet1 to this array* formula:

=IF(LEN(INDEX('Sheet 2'!$B$2:$B$6,MATCH(1,('Sheet 2'!$A$2:$A$6=A2)*('Sheet 2'!$H$2:$H$6=H2),0)))=15,"WUR",INDEX('Sheet 2'!$B$2:$B$6,MATCH(1,('Sheet 2'!$A$2:$A$6=A2)*('Sheet 2'!$H$2:$H$6=H2),0)))

and then you can copy it down.

*Please note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual < Enter >.

Hope this helps.

Pete

I am rather terrified of array formulas, but that's because I have never learnt how to construct them properly"

@tomm8810's - your post got me thinking about relative file sizes etc - and I was surprised by the result.

Using @JDobbsy1987's file and increasing the data to approx 5000 rows in both sheets 1 & 2:
with original formula : file size = 682kb,
with @Pete_UK's solution : file size = 754kb (more complex formula)
with @kev_'s solution : file size = 751kb (2 additional columns of "simple" formula)

Isn't Excel great - so many ways to cook the cake!
My new year resolution for 2017 - get to grips with array formula

Thank you for the replies, i did get it working using the method posted by Kev to begin with but will look into the array formula too.

Thank you very much for your support.

Cheers,
Jamie