PDA

View Full Version : Vlookup help



anthony20069
01-20-2009, 02:49 AM
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

xld
01-20-2009, 03:57 AM
Try this array formula

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

anthony20069
01-20-2009, 04:10 AM
Sorry forgot to mention, they are in two sperate workbooks, I have attached an example spreadsheets if this helps.

Thanks for your help

xld
01-20-2009, 04:24 AM
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
01-20-2009, 04:41 AM
Every time i Do that is keeps saying #N/A.. the password is jan2009

xld
01-20-2009, 05:39 AM
Did you array enter it?

anthony20069
01-20-2009, 06:00 AM
=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))

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

xld
01-20-2009, 06:04 AM
That is right. If you array enter it, Ctrl-Shift-Enter, it will work fine.