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

@SPark
@SPark ✭✭✭
edited 06/11/20 in Formulas and Functions

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):


Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    @SPark ✭✭✭
    edited 02/17/20

    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.
    Genevieve P. Employee Admin

    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
    Paul Newcome ✭✭✭✭✭✭
    edited 02/19/20

    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.

  • @SPark
    @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
    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.

  • 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!