Struggling with Formula. Not sure if using correct one.

I have 2 sheets.

We'll call Sheet A "Roster" and Sheet B "Grading Sheet"

Sheet A has columns for "Employee Number" and "Email Address"

What I am trying to do is on Sheet B, have a column in which you can enter the employee number, it reference Sheet A and then it will automatically enter the email address associated with that row on Sheet B in the adjacent column.

I have the sheets referencing, but not sure if I did that right either, along with a "VLOOKUP", but I think I have it all wrong.

Can someone help?

Find more posts tagged with

Formulas

Accepted answers

KPH

That formula looks good. It needs to be in the email cell - where you want the email to appear. The yellow ones here:

Can you try the cross reference again? Double click on the formula and put your cursor onto the reference like this:

Then click "Edit Reference" on the pop up.

Make sure you have highlighted the two columns you need, that the email address is in the second one, and your "Sheet reference name" looks exactly like this

🤞

All comments

KPH

It sounds like you've done it!

This is the VLOOKUP you need

=VLOOKUP([Employee number]@row, {Sheet A Range 1}, 2)

This is how it looks in context

[Employee number]@row is telling it to look at the column called Employee number n sheet B (the current sheet)

Maybe the cross reference (the part in bold) is wrong between sheet A and B. Here is an example:

I just selected the 2 columns. Email address is the second one. The 2 at the end of the VLOOKUP means the formula will look in column 2 in the range you selected.

This is my sheet A

Jason F

On Sheet B, am I putting the formula in Emp Num column or email column? I'm still getting an unparse.

KPH

Put the formula into the cell you want the result to appear in - the email column.

Can you copy and paste your formula here - I can check for commas and quotes and things?

Can you check for column heading spellings/case?

Jason F

Ok, done. The formula is pasted below. Maybe I have the cell references wrong as well. Which column should I enter the reference in?

=VLOOKUP([Instructor Employee Number]@row, {Roster MV Cycle Jun23-Feb24 Range 1}, 2)

KPH

That formula looks good. It needs to be in the email cell - where you want the email to appear. The yellow ones here:

Can you try the cross reference again? Double click on the formula and put your cursor onto the reference like this:

Then click "Edit Reference" on the pop up.

Make sure you have highlighted the two columns you need, that the email address is in the second one, and your "Sheet reference name" looks exactly like this

🤞

Jason F

I re-entered the formula and that seemed to work!! But now, it's pulling the wrong data over so I'm looking into that.

KPH

Good news and bad news. 😀

If the email address is not the second column, change the "2" in the formula to the number of the column that contains the email address.

(also, if you can, return to the answer above and click "yes" next to "Did this answer the question?" so others know the question was answered)

Jason F

I created a hidden column for #2 on sheet A since it only will pull adjacent columns for references. It's pulling the emails but regardless of the employee number it's pulling the same, wrong email address.

I did mark it answered.

KPH

Strange!

Check [Instructor Employee Number]@row is used and not [Instructor Employee Number]1 or similar

Check the data doesn't have the "wrong email address" duplicated

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.