Excel multi column search, custom text display

0
votes

Is it possible to return a given custom value while performing a multi-column search in excel?

I have 2 sheets, In the first sheet I have 3 columns, and each column have several rows with unique numbers.

In the second sheet I have 2 columns, the 1st column in this sheet2 has a unique number that can be found in sheet 1 columns 1,2 or 3.

In column 2 in this sheet2 I want to display a custom text if the value in column 1 is found in any of the 3 columns in sheet 1.

For example if the value in sheet2/column1 is found in sheet1/Column3, the text should be = "Value Found in Column1", if the value is found in sheet1/Column2, the text should be = "Value Found in Column2", if the value is found in sheet1/Column1, the text should be = "Value Found in Column1".

Is this possible, can you give an example of this?

Thank you.

1
excelexcel-formulavba
=IF(COUNTIF(Sheet1,A:A,A1),"Found in 1" ,IF(COUNTIF(Sheet1,B:B,A1),"Found in 2", IF(COUNTIF(Sheet1,C:C,A1),"Found in 3","Not Found"))) – Scott Craner
Using countif() is the easiest, but I've had issues with it returning incorrect values in Excel 2013. – user2027202827

1 Answers

1
votes

Yes this can be done. I could write the function for you but I think you would be better off trying to write it yourself. Here is how I would go about it. Write a vlookup() in columns B, C, and D of sheet2 corresponding to the columns A, B, and C of sheet1. If vlookup() returns a match in any of these columns, then that value exists in one of the three sheet1 columns.

The next step is to use isna() to return a boolean value of whether vlookup() returned a match. Again, you'll need one of these for each vlookup() (three total).

Lastly you can use and() on the isna() columns to check if all three are #N/A. Based on that boolean value, you can you if() to return your custom text.

Obviously the process I described splits the problem up into many columns. These functions can be combined to fit into one horribly nested function and put into one cell if you really want, but that's up to you. Let me know if you have any trouble.