Check Another Sheet for Value across Multiple Columns
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!
Comments

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.

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.

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!

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 🙂

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.

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 multiselect column so that the multiple options show up:
Cheers,
Genevieve

Thanks @Genevieve P.

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

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

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?

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. 🙂

I was able to find a workaround using the VALUE function (helper column).
Thank you, though :)

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.
Help Article Resources
Categories
Check out the Formula Handbook template!