Vlookup help

[anthony20069]

Hi guys,

Need your help (once again )... With a Vlookup is it possible to match two rows and then put in the value.... so...

In one sheet A1 is Surname B1 is Forename in F1 is Completed (contains Yes or No)... in another sheet I need to match the surename and forname to give me what there completion status is (yes or no), the problem is if I just do it with A1 there are a few people with the same Surname same goes for B! Forename.

Any suggestions?

Thanks very much

[Bob Phillips]

Try this array formula

=INDEX(Sheet1!F1:F100,MATCH(1,(A1=Sheet1!A1:A100)*(B1=Sheet1!B1:B100),0))

[anthony20069]

Sorry forgot to mention, they are in two sperate workbooks, I have attached an example spreadsheets if this helps.

Thanks for your help

[Bob Phillips]

It is password protected.

But all you need to do is open both spreadsheets, and then enter that formula, pointing at the other book when you select the range(s).

[anthony20069]

Every time i Do that is keeps saying #N/A.. the password is jan2009

[Bob Phillips]

Did you array enter it?

[anthony20069]

[VBA]=INDEX('[sample1.xls]CLEARANCE SPREADSHEET'!$F$2:$F$35,MATCH(1,($B2='[sample1.xls]CLEARANCE SPREADSHEET'!$A$2:$A$35)*($C2='[sample1.xls]CLEARANCE SPREADSHEET'!$B$2:$B$35),0))[/VBA]

This is the forumla that I have entered. No error comes up when I put it into the cell....

[Bob Phillips]

That is right. If you array enter it, Ctrl-Shift-Enter, it will work fine.