Cross Sheet formula from two different columns

Hi Friends,

I am currently using sheet A that has its rows automatically filled up by bringing the value from another sheet B using the VLOOKUP function. For example, I manually fill up "BIM ID" column in sheet A and hence the "SO" column in sheet A is filled up automatically by cross-checking sheet B using the formula: =VLOOKUP([BIM ID]@row, {B Range 1}, 3, false).

I need to edit the Vlookup formula in sheet A to have it cross-check two values at the same time from sheet B, which are the "BIM ID" and "Asset tag". So, in the case I manually fill up a BIM ID value in sheet A, the "SO" column in sheet A would automatically fill up based on the value found on sheet B. If I fill up "Asset tag" value in sheet A, the "SO" coloumn in sheet A would automatically fill up on the value found on sheet B as well, which does not currently happen due to the current Vlookup formula and would show as #NO MATCH.

Is there a formula that I can use to achieve my intent?

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Would you have both the BIM ID and the Asset Tag at the same time and need to match on both at the same time, or i it more of a "one or the other" type of thing?

  • One or the other type of thing.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Assuming they are also two separate columns in the reference sheet as well, you would use something like this:

    =INDEX({Reference Sheet SO Column}, MATCH(IF([BIM ID]@row <> "", [BIM ID]@row, [Asset Tag]@row), IF([BIM ID]@row <> "", {Reference Sheet BIM ID Column}, {Reference Sheet Asset Tag Column}), 0))

  • Hi Paul,

    The formula still did not work. To elaborate on the ask, below are screenshots of sheets A & B that I was referring to. So If I manually key in BIM ID column circled in blue in sheet A, the SO Number Bottom column circled in green in sheet A would automatically fill up based on matching with the Nortek SO# Bottom in sheet B in green with the Equipment Tag BIM ID# in sheet B circled in blue. If in the next row I manually key in the Asset Tag column shown in red in sheet A, the same automatic process would occur to fill in SO Number Bottom in green.

    I adjusted the formula you sent to reference the columns as follows and it showed #UNPARSEABLE as shown in the screenshot below.

    =INDEX({{SGA FCW Production Schedule Range 3}},MATCH(IF([BIM ID]@row <> "", [BIM ID]@row, [Asset Tag]@row), IF([BIM ID]@row <> "", {SGA FCW Production Schedule Range 4}, {SGA FCW Production Schedule Range 5}), 0))

    Note that "SGA FCW Production Schedule" is sheet B I am referring to in this discussion. I just edited the sheet name for the screenshot for clarity.

    I think the formula is not working because the "BIM ID@row" and "Asset Tag@row" form sheet A are not being called out in the If function as it would in the Vlookup function.


  • Hollie Green
    Hollie Green ✭✭✭✭✭
    Answer ✓

    I haven't dived in deep but one thing I noticed is on the first reference you have two sets of {} this can give an error.

  • The formula worked! Thank you so much for the support!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!