Cross Sheet formula from two different columns

Yousef J.

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?

Hollie Green

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.

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?

Yousef J.

One or the other type of thing.

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

Yousef J.

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

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.

Yousef J.

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