I'm getting an Error - Invalid Value for a formula - Need help

I have Sheet 'A' - which has Office numbers in a Column called Office No. (Column Type - Text/Number)

And I want to get Employee Information in a Column called Employee Info (Column Type - Text/Number)

The Employee Information is in Sheet 'B' which has the following columns

Office No. (Primary Column) = Similar info as Office No. in Sheet 'A'
Job Title (Column Type - Text/Number) = Which has various titles like PM, Eng, etc
Employee Name (Column Type - Text/Number) = Name of the employee based on their office & title

I was able to successfully use the below formula to extract the information, however, when I try to drag it down it gives me "INVALID INPUT" as an error.

=INDEX({Employee Name}, MATCH("PM", {Job Title}, 0), MATCH([Office No.]@row, {Office Number}, 0))

WHAT AM I DOING WRONG? OR IS THERE A DIFF FORMULA I CAN USE?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

The problem is the second MATCH. That portion of the INDEX function tells which column to use when a multi-column range is being referenced. IT sounds more like you would need an INDEX/COLLECT.

=INDEX(COLLECT({Range to pull from}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria), 1)

JuhiS_AD11

Hi Paul,

Thanks for your input, I tried the above formula (INDEX(COLLECT.....) and am still getting Invalid Value.

When I tried the INDEX MATCH formula from my initial post, I was able to get the correct information extracted from the corresponding spreadsheet, however when I tried to drag the info, I was given the invalid value error.

Paul Newcome

If the dragfill is what caused the issue, try locking in the [Office No.]@row reference.

=INDEX({Employee Name}, MATCH("PM", {Job Title}, 0), MATCH($[Office No.]@row, {Office Number}, 0))

JuhiS_AD11

So when I drag the formula across a Row (Left to Right) it copies the formula correctly.

However when I drag the formula downwards or across a column (up to down), it gives me Invalid Value.

I'm trying to figure out what I am doing wrong.

Paul Newcome

That's odd. Are you able to provide some screenshots?

JuhiS_AD11

There are the 2 sheets aka screenshots I am cross referencing.

The 1st screenshot is where I was able to use the following formulae to get the information under OM, RM, TD & DVP.

=INDEX({Employee Name}, MATCH("OM", {Job Title}, 0), MATCH($[Office No.]@row, {Office Number}, 0))

*Only changed PM to OM but I got the info across the column populated using the formula.

Here are a few things to note in my 2nd screenshot (info not visible)

I have multiple office numbers and under the category column - multiple OMs, RMs, TDs, etc.

Eg. 0005 - DVP - XYZ

0005 - OM - 123

0005 - OM - 456

So Qs --> when the formula is shifting through the data - does order of priority or order come into play - which could cause my data to go wrong??

Paul Newcome

Try an INDEX/COLLECT.

=INDEX(COLLECT({Name Column}, {Office Column}, @cell = [Office No.]@row, {Category Column}, @cell = "OM"), 1)

JuhiS_AD11

I get the #UNPARSEABLE error

Paul Newcome

Double check that I spelled the [Office No.] column correctly. If that still doesn't work, please provide a screenshot of the formula in the sheet similar to the screenshot below.

JuhiS_AD11

Paul Newcome

You are missing a comma between the first closing parenthesis and the 1.

JuhiS_AD11

I corrected that but now it says invalid value

JuhiS_AD11

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.