Need VLOOKUP to pull duplicate values in a specific order

BB2791

Hello,

I'm importing an Excel sheet into Smartsheet every week and then using a VLOOKUP to pull the most recent balance amount for PO numbers in a sheet. For some POs there is the same reference number but the PO amount is split amongst different projects and therefore the balance for those rows should be different (see yellow highlighted rows).

I'm interested in knowing if I can pull in the balance value in the same row order as the imported sheet. It's currently recognizing the PO amount is different for the same reference number but it's pulling the same balance for both rows

It should have $2856.74 for the first row and then $6070.55 for the second row (both reference PO# 223044). Is there a way to do this?

Kelly Moore

Hey @BB2791

This approach needs a system column on your green sheet, like the auto-number column [Row ID], to give you a positional reference down the sheet. You will need to add this to the sheet if you do not have it already. The COUNTIFS function is providing the row_index needed in the INDEX function.

I assumed that the green portion was a different sheet. Remember when using cross sheet references you must manually build the references rather than just copy pasting the formula below. Also, in case you are not familiar with the INDEX/COLLECT combo function - note that I referenced single columns not the entire table as you would with a VLOOKUP.

=INDEX(COLLECT({Yellow sheet column Current Period Open Commitment}, {Yellow sheet column PO Number}, [ID #]@row), COUNTIFS([ID #]:[ID #], [ID #]@row, [Row ID]:[Row ID], @cell <=[Row ID]@row))

Will this work for you? Shout out if you need any help with the cross sheet references

Kelly

BB2791

Hi Kelly,

Thanks for looking into this for me. You're correct that the white/yellow sheet is a different sheet from the green one. I've never used the INDEX/COLLECT function before but it seems useful.

A few questions - I went ahead and added the [Row ID] auto-number column to the green sheet. Where does the [ID #] reference comes from? Is that the same thing as [Row ID]? Also I'm unclear what you mean by, "you must manually build the references rather than just copy pasting the formula below. " Does this mean I can't set up the formula and then copy down the column?

Thank you!

Kelly Moore

@BB2791

The [Row ID] is from the auto-number column you added. In the green sheet in your screenshot, it appears the PO number is in a column called [ID #]. Is that not what it is called in the green sheet?

In terms of the cross sheet references, I meant you couldn't just copy the formula from the post and insert it into your sheet. Sorry, I didn't mean to confuse you - you used this when you were building your vlookup.

You will be able to convert the formula into a column formula to automatically copy down the column.

Does this answer all your questions? Shout out at me again if not. If you can, use the @ sign to call me out so I see it in my inbox.

Kelly

BB2791

Hi @Kelly Moore ,

Thanks for the clarification. I accidentally forgot about the ID # column (duh) I had so that makes sense. I revised the formula you provided and customized it to my specific reference. It looks like this now:

=INDEX(COLLECT({MRI Purchase Import 8.3 Range 1}, {MRI Purchase Import 8.3 Range 2}, [ID #]@row, COUNTIFS([ID #]:[ID #], [ID #]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row))

Unfortunately, I'm getting an "Incorrect Argument" error. Do you know what I can tweak to get it working? The yellow sheet name is "MRI Purchase Import 8.3" if that's helpful.

Kelly Moore

Hey @BB2791

You inadvertently are missing a parenthesis. Close the COLLECT function with a parenthesis after [ID #]@row.

does that fix your error?

Kelly

BB2791

Yes @Kelly Moore that worked! Thank you so much!!