Index formulas replacing VLookUp Formulas

Dawn McCallister

Hello

It has been recommended that I replace my vlookup formulas with Index(Collect) formulas. This is my first experience with the Index and / or Collect Match concept and honestly I am struggling on my first try. I am trying to pull in a total funds approved value from the my budget sheet to my portfolio sheet.

Both Sheets start with a column title OPS# - this would be the matching data value and I want to pull the value from the column/cell match "Total Funds Approved". The are name the same on both sheets.

English statement (to the best of my ability) building the formula in the column titled "Total Funds Approved" on the Operations Project Log. Find the matching OPS# from the Operations Project Log on the Funds Approval Log in Column titled "OPS#. If match found then pull to the Operation Project Log the value that is in the column titled "Total Funds Approved" on the Funds Approval Log to the Operations Project Log.

Looking for a little help with this learning curve.

Thank you

Dawn

DW1

You only need Collect() if you need to use an arithmetic function as part of your formula.

If you only need to lookup a single value, you can use something like this:

=INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),2)

This assumes that the Total Funds Approved column is 1 row to the right of the OPS# column.

DW1

The last part of the formula is the column index reference. So if your range is 21 columns across, and the value of interest is in the 21st column (your Total Funds Approved value), the last number needs to be 21.

=INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),21)

DW1

You only need Collect() if you need to use an arithmetic function as part of your formula.

If you only need to lookup a single value, you can use something like this:

=INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),2)

This assumes that the Total Funds Approved column is 1 row to the right of the OPS# column.

Dawn McCallister

@DW1 What is the impact if the "Total Funds Approved" column is 20 columns to the right?

DW1

The last part of the formula is the column index reference. So if your range is 21 columns across, and the value of interest is in the 21st column (your Total Funds Approved value), the last number needs to be 21.

=INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),21)

Dawn McCallister

@DW1 - That work great! Thank you so much for your assistance.

Dawn McCallister

@DW1

Because you were so helpful on my question regarding Index(Match) formulas. I need a little more help. Today my Index(Match) formula is erroring and the only change I can identify is that I am using a new set of sheets. When I create the formula it is adding an additional parenthesis at the end of the formula that does not exist in previous.

Here is the details

Sheet 1 - OPS Tracker

Sheet 2 - Cap Request Log. This is the sheet where I am creating the Index(Match) formula

The goal is to find the OPS number from the Cap Request Log on the OPS Tracker and return the Project Phase. If no match found than leave blank.

OPS # is the first column on both sheets. Project Phase is column number 26 on the Ops Tracker. Column Names do not mirror each other on both sheets (Ops # vs Ops Number)

Here is the formula as I have it written.

=IFERROR(INDEX({2022 OPS Project Track - Index for Prj Phase}, MATCH([OPS Number]@row, {2022 Operations Project - OPS#}, 0, 26), ""))

Results = #Incorrect Argument Set

Help?

Dawn McCallister

@DW1 - I actually finally figured it out. YAY