Check Another Sheet for Value across Multiple Columns

Art Schneiderheinze

In Sheet 2, I am looking to search Sheet 1 for an instance of a value, that could be in one of four columns.

Sheet 1

COLUMN A | COLUMN B | COLUMN C | COLUMN D | COLUMN E |

ABS 123      |  March         |   26444         |                     |   43454        |

ABS 456      |  June           |   87834          |   45003        |                     |

ABS 789      |  August        |                      |                     |                     |

ABS 000      |  October       |                      |                     |    36543       |

 

There are four order numbers in Sheet 1 (ABS 123, ABS 456, etc.) in Column A. Column is the month the order was processed. Columns C to E represent different phases in processing. There may be a processing number in one of the columns.

In Sheet 2, I'd like to find instances of the order number and processing number. For example, if I "search" for ABS 123 and 26444, it would report "March", because it searched Column A and Columns C to E for both the order number in Column A (ABS 123) and the processing number in Columns C, D, and E (26444), and if found, displays the month in Column B (March). Otherwise, it displays "N/A". 

Sheet 2

COLUMN A | COLUMN B | COLUMN C 

ABC 012      |  23000         |  N/A

ABC 123      |  26444         |  March

ABC 123      |  32322         |  N/A

ABC 123      |  43454         |  March

ABC 331      |  42232         |  N/A

ABC 456      |  45003         |  June

ABC 456      |  87834         |  June

ABC 789      |  12345         |  N/A

ABC 000      |  36543         |  October

 

Sheet 2 already contains information in Columns A and B. The formula appears in Column C, which references Sheet 1 and looks for the value in Column A from Sheet 2 in Column A of Sheet 1 and Column B from Sheet 2 in Columns C to E of Sheet 1 and either displays the value of Column B of Sheet 2 or N/A (not found).

I get how to use the INDEX/MATCH function, but not sure how to have it look for a value (Column B in Sheet 2) in multiple columns (C, D, and E) in Sheet 1.

Thanks for your guidance!

Paul Newcome

Try something like this...

 

=INDEX({Month}, MATCH([Column A]@row, {Column A}, 0), IF(MOD(MATCH([Column B]@row, {Columns C - E}, 0), 3) = 0, 3, MOD(MATCH([Column B]@row, {Columns C - E}, 0), 3)))

 

EDIT:

Please note: The divisor within the MOD function would be however many columns you are using in your range. The number in the "if_true" section of the IF statement will also be the same as the number of columns you have in your range.

Paul Newcome

Please disregard the above solution. It is not what you are looking for. I will do some more testing and see what I can come up with.

 

My apologies.

Chiu Bar

Hello @Paul Newcome ,

This is an interesting topic and it is similar to what I am currently working on.

Do you happen to have an update on this?

Your help will be greatly appreciated.

Cheers!

Genevieve P.

Hi @Chiu Bar

Can you clarify your specific scenario? (E.g. what your source sheet looks like, what you want to bring back into your new sheet). Screen captures would be helpful, but please block out sensitive data 🙂

Chiu Bar

Hello @Genevieve P. ,

Sheet 1 has the following columns:

Data is drawn from a form with each row representing a data set.

Second through third columns of Sheet 2 contains formula to determine the entries provided for each Property Name and expected to have the following results:

I was able to the get the count on the No. of Submissions by applying the formula on Column 2: COUNTIF({[Sheet 1] Property Names}, =[Property Name]@row).

Where [Sheet 1] Property Names encompasses columns Type A, Type B, and Type C.

However, I was unsuccessful in determining the value for Assigned Team for each row under Property Name using this formula: INDEX({[Sheet 1] Assigned Team}, MATCH([Property Name]@row, {[Sheet 1] Property Names}, 0)).

An empty value came out on the cell, though.

What do you think is wrong with the formula that I have applied?

Any help will be greatly appreciated.

Genevieve P.

Hi @Chiu Bar

Thanks for clarifying! This is really helpful.

What would you like to happen if there are more than one values in the source sheet? For example:

What I would do here is use 3 separate JOIN(COLLECT formulas, each looking at an individual Type column, like so:

=JOIN(COLLECT({Assigned Team}, {Type A}, [Property Name]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Assigned Team}, {Type B}, [Property Name]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Assigned Team}, {Type C}, [Property Name]@row), CHAR(10))

Then I'd put this in a multi-select column so that the multiple options show up:

Cheers,

Genevieve

Chiu Bar

Thanks @Genevieve P.

Chiu Bar

Hello again @Genevieve P.

I have applied the formula on the "No. of Submissions" column. However, when I need to get the aggregate (using the SUM function) on the said column, it returns no value.

I have modified the same column to make it as "=[Property Name]@row" and still returns no value.

May I be further guided on this.

Thank you

Genevieve P.

Hi @Chiu Bar

Can you post screen captures of your sheet and the current formula you're trying? (But please block out sensitive data)

Chiu Bar

Thanks for the quick reply @Genevieve P. .

In reference to the above screenshot, the formula applied on the yellow cells are similar to:

=JOIN(COLLECT({Assigned Team}, {Type A}, [Property Name]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Assigned Team}, {Type B}, [Property Name]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Assigned Team}, {Type C}, [Property Name]@row), CHAR(10))

Taking the sum on selected cells (for testing purposes) gave a zero value.

If I use "+" on the values, it resulted to concatenation of the values.

Any thoughts for a workaround?

Genevieve P.

Hi @Chiu Bar

Thank you, this helps!

It looks like the JOIN formulas are bringing together the values so they're seen as text, not numerical. Can I ask why you've switched to JOIN from the COUNTIF formula above?

You should be able to add the results of a COUNTIF formula with other results to get a number. When you say that the formula is "similar" to the JOIN formula above, is it the same but referencing a different column? Or are you using COUNTIFs? This is the formula we'll want to look at so that you can eventually SUM the results. 🙂

Chiu Bar

I was able to find a workaround using the VALUE function (helper column).

Thank you, though :-)

Genevieve P.

Hi @Chiu Bar

I'm glad you found something that works for you! However keep in mind that if your previous formula is reading the numbers as text values, it might place two values together (like: 6 and 6 to show 66). Then the VALUE function will read this as 66 instead of 12. Is that a possibility?

I would recommend adding multiple COUNTIFS together instead of using JOIN if you are looking into multiple sheets:

=COUNTIFS(....) + COUNTIFS(....) + COUNTIFS(....)

This should give you the correct value across sheets and eliminate the need for a helper VALUE function.