Nested VLOOKUP statement error

Sarah Brelage · 12/20/19

I am receiving an error with the following nested VLOOKUP statement. I've included an image that might help to better explain. Unfortunately, I have looked at this so many times and tried so many combinations that I'm afraid it is a simple fix that I am just not seeing.

Need to use the number in Details4 cell (in this case ProjectId=1) to look up on the Intake Form sheet for the Row with Project ID = 1, then go to column 50 (which is a checkbox). If the checkbox is checked then the Status field needs to say "Complete" otherwise it can say "In Queue"

Paul Newcome · 12/20/19

Try something like this instead...

=IF(INDEX({Other Sheet Checkbox Column}, MATCH(Details4, {Other Sheet Project ID Column}, 0)) = 1, "Complete")

This section:

INDEX({Other Sheet Checkbox Column}, MATCH(Details4, {Other Sheet Project ID Column}, 0))

will basically pull whether or not the box is checked in whatever row the project id is found on.

Then we drop that in an IF statement to say that if the result of the INDEX/MATCH equals 1 (the box is checked) then output "Complete".

Paul Newcome · 12/20/19

The difference between yours and mine is that I did not specify a column number. That is why there are two closing parenthesis at the end of mine. One to close the MATCH then another to close the INDEX.

Because you are specifying a column number, You will want to close the MATCH with one closing parenthesis, then continue your INDEX statement from there.

=INDEX(range, row #, Column #)

You will want to close the MATCH and drop it into the row # portion.

=INDEX(range, MATCH(.......), Column #)

Just a note... Because you only need to return data from one column, you can reference only that one column in your Range 5 and skip over the column number in the INDEX function. That's one of the many perks to using INDEX/MATCH. The columns referenced can be completely independent on each other.

To fix your formula:

=INDEX({Project Team Range 5}, MATCH([Responsible Dept]27, {Project Team Range 3}, 0), 2)

To maximize flexibility taking advantage of the ability to reference independent columns...

=INDEX({Project Team Name Column Only}, MATCH([Responsible Dept]27, {Project Team Range 3}, 0))

A couple of notes:

In both of the final formulas, there are only 2 closing parenthesis. That is because we are only using 2 functions. A third closing parenthesis anywhere without a matching opening parenthesis will always cause an issue.
Note the lack of a column number in my formula. Selecting only the column we are pulling from for the range in the INDEX function means that the source sheet can now be reorganized in regards to the column positions however you want as many times as you want without breaking anything. Referencing two columns for the INDEX range means that those two columns MUST ALWAYS remain adjacent and in that specific order which completely negates the flexibility of this combo.

Paul Newcome · 12/20/19

Try something like this instead...

=IF(INDEX({Other Sheet Checkbox Column}, MATCH(Details4, {Other Sheet Project ID Column}, 0)) = 1, "Complete")

This section:

INDEX({Other Sheet Checkbox Column}, MATCH(Details4, {Other Sheet Project ID Column}, 0))

will basically pull whether or not the box is checked in whatever row the project id is found on.

Then we drop that in an IF statement to say that if the result of the INDEX/MATCH equals 1 (the box is checked) then output "Complete".

Sarah Brelage · 12/20/19

Awesome. It worked like a charm. I had was going back and forth about possibly using the INDEX but haven't used it much so I dismissed it quickly. Thanks for the help!!

Paul Newcome · 12/20/19

Happy to help! 👍️

Honestly... I haven't used VLOOKUP in a very long time. INDEX/MATCH is SO much more flexible as it allows for reorganization of the source sheet without breaking the formula because you can reference each column individually. It also means that the order of the columns doesn't matter so you don't have to worry about which one is on the right or left.

Here's a breakdown of how it works...

=INDEX(range to pull from, row number, optional column number)

The range to pull from is the obvious part. That is the data you want to pull.

The second portion where you specify the row number is where MATCH comes into play.

MATCH will return a numerical value based on which cell within a range specific data is found. When referencing an entire column, the returned value becomes the row number.

=MATCH(data to search for, range to search in, match type)

I have found that using 0 (zero) for the match type provides the most consistently accurate results.

So if you are only looking at one column to pull data from and one column to match on, you would build the MATCH formula and just drop it into the second portion of the INDEX function.

You can also use a second MATCH function in the third portion of the INDEX function if you are pulling from a table so that row and column numbers are both automated based on your specified criteria.

I definitely strongly recommend familiarizing yourself with the INDEX/MATCH combo in place of the VLOOKUP as it provides a lot more flexibility and will make things easier in the long run.

(SIDE NOTE: The COLLECT function also brings a huge level of flexibility once you get the hang of it. You can essentially use this to turn any function into a functionIF kind of thing.)

Sarah Brelage · 12/20/19

=INDEX({Project Team Range 5}, MATCH([Responsible Dept]27, {Project Team Range 3}, 0)), 2)

So here is the one I'm trying on my own...Project Team Range 5 is made up of two columns, Role and Name. Responsible Dept is the Role in my sheet. Project Team Range 3 is one column and it contains the many options to match with Responsible Dept. The 2 was referencing the second column in Project Team Range 5 contains the Name that needs to be returned.

It keeps giving me an error and I've been through this several times and I matched your example etc. so I'm not stuck

Paul Newcome · 12/20/19