Vlookup help

**hjs2007** · 06-25-2013, 01:57 PM

I have a company and I am trying to clean up my Excel monthly expendtiures. I have two seperate excel sheets of expenditures and want to be able to link them together so I can plug a number into one and have it automatically update to the other. I believe I need to somehow use a V lookup function but am having trouble piecing it together. How can I plug into A and have it automatically update B? Thanks!

**BB1972** · 06-25-2013, 02:51 PM

hjs, have a look at what I've highlighted green in Sheet "B" on the attached. I have used a conditional INDEX/MATCH array formula to return your expenditure on Sheet "B" once it has been input in Sheet A.

A few things to note - your headings and dates on both sheets must match - Excel will not see 5-Jun and 05/07/2013 as the same as each other, because they are not the same. Likewise, "After School Expenses" (or whatever it was called) is not a match for "After School".

The formula in Cell B7 of Sheet "B":

Please Login or Register  to view this content.

says: return from Column F of Sheet A, where there is a match for Cell A7 of Sheet "B" in Column E of Sheet A, provided the corresponding cell of Column E is equal to the contents of Cell B4 of Sheet "B". The "0" specifies that you require an exact match, and the formula is wrapped in an IFERROR function which will return "-" if there is no match.

The formula must be committed/entered by pressing the Ctrl, Shift & Enter buttons at the same time.

I have done the "After School" & "Food Service" categories for you - have a go at "Salary" & "Utilities", and shout if you need more help.

Hope this helps.

**hjs2007** · 06-25-2013, 03:12 PM

Working on it now and will let you know, thank you so much for your help!

**hjs2007** · 06-25-2013, 05:29 PM

Thank you so much for your help it was awesome! It does exactly what I needed to do. However I am having trouble seeing how you expanded from one column to another. I took out all the information and was able to follow your steps to rework the first "after school" comumn butI am having trouble reading the formula and knowing why and how you switched the letters. Could you just expand a little in words explaining the difference between the first and second formula. Thanks!!

**BB1972** · 06-25-2013, 05:37 PM

Sure, no problem. When you say "the first and second formula", do you mean the difference between the formulas in Column B & C?

**hjs2007** · 06-25-2013, 06:15 PM

Yes. I am confused why you changed what you did and how it applied ex. from F:F to G:G, just have troubles understanding the formulas. Thanks so much!

**BB1972** · 06-25-2013, 06:29 PM

Apologies - that was an error that I've just noticed. The only thing that should change in the formula in B7:

Please Login or Register  to view this content.

when it's moved to C7:

Please Login or Register  to view this content.

is the reference to the expense type - as you appear to have figured out, all the other columns should stay the same, because we still want the $ amount returned, and we're still checking the same column for dates, etc, regardless of which column in Sheet "B" the formula is in.

Sorry for any confusion I may have caused, and thanks for the rep. Attached is a file with the formulas corrected.

**hjs2007** · 06-26-2013, 11:30 AM

Your help has been unbelievable and I am so appreciative of it, thank you!! One more question and I dont know if it is even possible so if not no worries.

On "B" I have the address of everyday in the year starting July 1- June 30 (I just attached July). In the excel version I uploaded at the end of each row I have a final value "x" that all adds up in a final column. However currently if I dont have a value say in 07/02/2013 at the end of the row I get the error message #Value!

Is there anyway to make a date that I do not have an expenditure on equal 0 instead of -- (the formaula) or is it better to attack that with a formula at the end of the row?

Thanks Again!!!