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

JuhiS_AD11

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?

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