Not sure which formula I need...

I am trying to pull data from the ISIR Pull 2023-2024 sheet and make it populate the information into another range on another sheet.

I want the ISIR Pull 2023-2024 sheet to match the Student ID to the Jones Scholarship sheet and pull in the FA23 HRS without having to hand-type it. (Work smarter, not harder, right?)

The thing is, the Jones Sch. sheet does not include all of the students. What formula do I use to make the FA23 HRS pull into the Jones Scholarship sheet from the ISIR Pulls 2023-2024?

I think this is an INDEX/MATCH formula, but I can't figure out even where to plug any of it in.

Thank you kindly for your time and assistance.

Find more posts tagged with

Formulas

help

Accepted answers

All comments

Kelly Moore

Hey @mwalkerMC

You are correct that you need an Index/Match - you only have one criteria to match. You would use an Index/Collect if you had multiple criteria you were matching to.

You will have to manually insert the cross sheet references using the INSERT REFERENCE From New Sheet in the formula window. Copy this formula in a cell then begin deleting each term one by one. After deleting a term, click the link in the formula window and to to your source sheet and select the correct column in that sheet. Change the name of the Range so you know what it is, then click the button to INSERT REFERENCE. Do this one by one on all the terms in the formula.

=INDEX({ISIR Pull 2023-2024 sheet FA23 HRS column},MATCH([Jones Scholarship Student ID]@row, {ISIR Pull 2023-2024 sheet Student ID column},0))

Will this work for you?

Kelly

mwalkerMC

Hi @Kelly Moore!

I appreciate your prompt response! Unfortunately, I got #UNPARSEABLE when doing what you instructed.

Here is the formula I used, and I made sure to delete and re-specify the "connections." What does the 0 at the end of the formula stand for?

=INDEX({ISIR Pull 2023-2024 sheet FA23 HRS column},MATCH([Jones Scholarship Student ID]@row, {ISIR Pull 2023-2024 sheet Student ID column},0))

I appreciate you taking the time to help a girl out. 😊

Meredith

Kelly Moore

@mwalkerMC

Unparseable errors typically come from misplaced commas, brackets or wrong column names. It doesn't look like you replaced the column name of your MATCH term. Change the name of [Jones Scholarship Student ID] to [Student ID]@row.

Did you actually create the cross sheet references, or did you copy paste the formula into your sheet?

Kelly

mwalkerMC

@Kelly Moore

I did cut and past and reconnected functions in the last round. This time I typed it all from scratch. Here is the updated formula:

=INDEX({ISIR Pull 2023-2024 sheet FA23 HRS column}, MATCH([Student ID]@row, {ISIR Pull 2023-2024 sheet Student ID column}, 0))

I got #INVALID REF and it looks like it's highlighting the "[Student ID]@row" of the formula. Hopefully this means we are on the right track this round.

Thank you again for your willingness to help me out.

Meredith

mwalkerMC

Wait! I did it!

=INDEX({ISIR Pull 2023-2024 Range 1}, MATCH([Student ID]@row, {ISIR Pull 2023-2024 Range 3}, 0)) got the correct information pulled!

Thank you, @Kelly Moore

Kelly Moore

@mwalkerMC

Well done! Nice job getting it to work.

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.