VLOOKUP? INDEX MATCH? invoice amounts from sheet 1 into sheet 2 based on a matching #

@SPark

Help please!

I'm looking for the invoice amounts from one sheet transfer into another sheet based on a matching CAR#.

For example: if CAR# FP2020-1 is listed in spreadsheet (B) then I want the invoice amount associated to populate into the $spent column in (A). I'm running into confusion because I can't figure out how to do this with spreadsheet (B) listing multiple of the same CAR#.

Any advise? (Paul are you out there?) 😆

(A):

(B):

Paul Newcome

Ok. So I think I may have something for you...

Check out THIS SHEET.

In the [$ Spent] column, the breakdown is in the child rows and the total of the child rows is summed up in the parent rows. If you do not want the sum in the parent rows, we could actually get rid of a column and just format the parent row cells to have the text match the background.

Basic Rundown:

At least 1 empty row above grid

[Car #]:

Parent Rows are manual entry of the Car #. Child rows contain

=PARENT()

[$ Spent]2 (dragfill down):

=IF(COUNT(CHILDREN([CAR #]@row)) > 0, SUM(CHILDREN()), String@row)

String2 (helper column/can be hidden) (dragfill down):

=IF(COUNT(CHILDREN([CAR #]@row)) = 0, VALUE(IF([Child Number]@row = 0, LEFT(PARENT(), FIND(",", PARENT()) - 1), IFERROR(LEFT(SUBSTITUTE(PARENT(), JOIN(COLLECT(String$1:String1, String$1:String1, ISNUMBER(@cell), [CAR #]$1:[CAR #]1, [CAR #]@row), ",") + ",", ""), FIND(",", SUBSTITUTE(PARENT(), JOIN(COLLECT(String$1:String1, String$1:String1, ISNUMBER(@cell), [CAR #]$1:[CAR #]1, [CAR #]@row), ",") + ",", "")) - 1), SUBSTITUTE(PARENT(), JOIN(COLLECT(String$1:String1, String$1:String1, ISNUMBER(@cell), [CAR #]$1:[CAR #]1, [CAR #]@row), ",") + ",", "")))), JOIN(COLLECT({Source Invoice Amount}, {Source CAR #}, [CAR #]@row), ","))

[Child Number]2 (helper column/can be hidden) (dragfill down):

=COUNTIFS([CAR #]$1:[CAR #]@row, [CAR #]@row) - 2

Full View:

Helper columns hidden:

Genevieve P.

Hi SPark,

Are you looking to return just one result of a specific row from the source sheet? (If so, how are you specifying the difference between the rows - by date, or by another criteria?) Or are you looking to SUM all of the amounts of CAR# FP2020-1 together? Or collect each instance and return it in one cell (for example: $553.43, $289.75... etc).

An Index Match formula would return one result, based on the position of the content in the lookup sheet: Smartsheet calculates the relative position of a search value by counting cells from left to right (across columns), then top to bottom (across rows). In a lookup table consisting of two columns, the cell in the top row of the leftmost column is the first position, 1. (You can read more about this in the MATCH Help Center article: here).

An INDEX(MATCH works like this:

=INDEX({Column that has the value you want returned}, MATCH([Value you’re looking to match]@row, {Column with the value you’re looking to match in the other sheet}, 1))

If you were looking to return all of the values from in Invoice Amount column that pertain to this CAR#, you could use a JOIN(COLLECT formula to bring all of the matching data into one cell in the other sheet.

Let me know what you're looking to accomplish and we can figure something out!

Cheers,

Genevieve

@SPark

The goal would be that spreadsheet (A) would end up like this:

If the CAR # from (B) matches the CAR# input into (A) then the invoice amounts associated with that row are listed.

**EDIT: I'm realizing in my screenshots the CAR# in (A) Doesn't match (B) but it would. (A) would read FP2020-01...not 02.

Genevieve P.

Thanks for clarifying!

You would need to have more criteria for each row to make it unique, in order to use something like an Index(Match. Since your CAR number is the exact same, the formula won't know which Invoice Amount to pull. The JOIN(COLLECT formula would bring all that information into one cell for you, but not into multiple like your example.

Could you use Cell Linking? (See here)

Otherwise, you may want to pull a Report instead, with the criteria being if the Invoice Amount or Total is not blank, sorted by CAR number (See here).

If neither of these options will work for you, it would be helpful to know a bit more about each sheet and what the purpose of the second sheet is.

Thanks!

Genevieve

Paul Newcome

I'm here. Haha.

I am thinking that maybe using a JOIN/COLLECT on the parent row to pull each of the entries and the parsing it down the child rows may be an option. It's just an idea right now though. I need to do some testing before I can confirm exactly how to make it work. I do have a few parsing solutions in my notes somewhere, so I will start with those and revisit once I have figured out if it is either not possible or possible and provide a solution.

EDIT TO NOTE: The parent row could have the text color changed to match the fill color so that the long string is not visible.

Paul Newcome

Ok. So I think I may have something for you...

Check out THIS SHEET.

In the [$ Spent] column, the breakdown is in the child rows and the total of the child rows is summed up in the parent rows. If you do not want the sum in the parent rows, we could actually get rid of a column and just format the parent row cells to have the text match the background.

Basic Rundown:

At least 1 empty row above grid

[Car #]:

Parent Rows are manual entry of the Car #. Child rows contain

=PARENT()

[$ Spent]2 (dragfill down):

=IF(COUNT(CHILDREN([CAR #]@row)) > 0, SUM(CHILDREN()), String@row)

String2 (helper column/can be hidden) (dragfill down):

=IF(COUNT(CHILDREN([CAR #]@row)) = 0, VALUE(IF([Child Number]@row = 0, LEFT(PARENT(), FIND(",", PARENT()) - 1), IFERROR(LEFT(SUBSTITUTE(PARENT(), JOIN(COLLECT(String$1:String1, String$1:String1, ISNUMBER(@cell), [CAR #]$1:[CAR #]1, [CAR #]@row), ",") + ",", ""), FIND(",", SUBSTITUTE(PARENT(), JOIN(COLLECT(String$1:String1, String$1:String1, ISNUMBER(@cell), [CAR #]$1:[CAR #]1, [CAR #]@row), ",") + ",", "")) - 1), SUBSTITUTE(PARENT(), JOIN(COLLECT(String$1:String1, String$1:String1, ISNUMBER(@cell), [CAR #]$1:[CAR #]1, [CAR #]@row), ",") + ",", "")))), JOIN(COLLECT({Source Invoice Amount}, {Source CAR #}, [CAR #]@row), ","))

[Child Number]2 (helper column/can be hidden) (dragfill down):

=COUNTIFS([CAR #]$1:[CAR #]@row, [CAR #]@row) - 2

Full View:

Helper columns hidden:

@SPark

That is one intense formula... 😧I knew I could count on your Paul, haha.

Also thanks for your help and suggestions Genevieve!!

I'm going to try both and see which one works best for this situation. Appreciate you both!

Paul Newcome

@Spark Happy to help! 👍️

That formula was not created as it currently is. I built it using one small piece at a time testing and tweaking as needed across multiple cells so I could watch exactly how each piece worked both on its own and with the other pieces. Then it was put together one piece at a time with more testing and tweaking as I went along until what you see was finally created.

Mike Bird

Hi @Paul Newcome, I think this is what I have been looking for, but I'm trying to make it work where the values are not numbers - is there a way to modify this formula from 'ISNUMBER' that would allow this?? Nothing I try seems to work!