Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

I need help with a formula to copy from one sheet to another if cells match

0

I have been struggling with this, keep in mind i dont know much about excel & the goblins will probably have a field day with me. if someone could point me in the correct direction that would help.

My workbook has two sheets: AWARDS TRACKER & MASTER.

AWARDS TRACKER get updated but i want to enter a No found in the AWARDS TRACKER in the master sheet then it will pull data to the master from the tracker, eg if i enter 5 in MASTER K7 it must find the "5" in AWARDS TRACKER A:A then copy the data from AWARDS TRACKER E7 to MASTER D2 (workbook attached)

i have this formula but my excel knowlage is lacking, have spent hour googleing it but to no avail

=VLOOKUP(K7;'AWARDS TRACKER'!A:A;COLUMN(D2)-COLUMN('AWARDS TRACKER'!A:A)+1;0)

i hope this makes some sence!

excel
posted by: excell noob (rep: 2) on Tue May 8, 2018 at 10:02 am
Answer
Discuss

Answers

0
Selected Answer

This is the formula for cell MASTER!D2.

=VLOOKUP($K$7,'AWARDS TRACKER'!$A$3:$E$39,3,FALSE)

It is much simpler than what you seem to have considered. It all revolves around the range 'AWARDS TRACKER'!$A$3:$E$39. The function looks for the look-up value (K7) in the first column of that range and returns the value from its 3rd column. FALSE signifies that an exact match is required.

Because this range is so important I recommend that you use a named range which you can use for all your formulas in the Master sheet. For example, 'AWARDS TRACKER'!$A$3:$AF$39. VLOOKUP would still look for a match in the range's first column and your formula in MASTER!D2 would still require the value from its 3rd column, but other VLOOKUPs could return values from any of the other columns.

To name such a range select A3:AF39 and type the desired name (for example AwardData) in the Name Bar (to the left of the Formula Bar). You can later modify the range's address using the Name Manager which you find on the Ribbon's Formulas tab. Doing so is easier than modifying the formulas that refer to the range. Once they work you will never need to touch them again.

If you adapt this method the formula first mentioned above would look like this:-

=VLOOKUP($K$7,AwardData,3,FALSE)
posted by: Variatus (rep: 4889) on Tue May 8, 2018 at 9:13 pm
Discuss

Discussion

Thanks Variatus, that did exactly what i needed.
excell noob (rep: 2) May 10, '18 at 1:43 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login