Excel Excel VLookup...

[Demtro]

Hi folks, I am having a problem coming up with a formula in excel that will use the vlookup function to pull data into one spreadsheet from a second spreadsheet....

I am currently using

=VLOOKUP(A9,'[Lakeland Cash Out Export (Autosaved).xls]test'!$B$2:$BA$37,2,FALSE)

and it is working, it is pulling the information in, however I need a function that will verify 2 pieces of data exist before pulling data in. Right now I am checking to see if a name exists in both spreadsheets and if it does then pull in the data in column 2....however I need the function to confirm that a name and a date are the same in both spreadsheets before it pulls any data in...

i guess i need a second vlookup using an and statement or something...not sure, but if anybody can help....

if you need a better explanation please let me know....

 

[Stoneboysteve]

Demtro,

Have you tried nesting the vlookup in iIF statements?
sorta psudocode:
=IF(name1 = name2,(IF(date1 = date2,(VLOOKUP(A9,'[Lakeland Cash Out Export (Autosaved).xls]test'!$B$2:$BA$37,2,FALSE)),"no date match"),"No name match")

I did not open Excel but you should be able to figure out the syntax of the nesting.

Also, if the spreadsheets or any of the cells are static using a named range will make the formulas easier to write.

Stoneboysteve

 

[Demtro]

Stoneboysteve,

thanks for the idea, but i am not sure it will work..it is more or less what i asked for but i thihnk i need to explain using an example. Below are 2 spreadsheet examples...spreadsheet A is where the data resides and spreadsheet B is where i want the data to go....


Spreadsheet A

Date Name Office Total
6/2/11 Bob Boise 5500
6/3/11 Tom Miami 3400
6/2/11 Tom Atlanta 1200
6/3/11 Bob Houston 1400

Spreadsheet B

Date 6/2/11

Name Office Total
Bob
Tom


Ok, now, I want to be able to bring over the office and the total for bob, on the date listed in spreadsheet B. the same for tom. my problem is since their name shows up more then 1 time, i want to make sure that I get the info for the correct date....

Suggestions?