Using SEARCH and IF function to find two columns from one source

[Tricke86]

Hi Guys

I hope someone can help, I'm currently doing a project at work where i need to search for data from on sheet on another search.

The trouble i am having is to search for two columns of data from one search source. I am using a SEARCH formula but i have no idea how implement the IF function i want formula to search department 1 if nothing found then search department 2 from data on search page in C5 but i have no idea if this can be done or how to do it.

here are the formulas -(-IFERROR(SEARCH(Search!C$5,Data!$I5),"")) this to search department 1 and -(-IFERROR(SEARCH(Search!C$5,Data!$J5),"")) department2

but at the moment they only return a search if both the data i enter in C5 is in both columns, this not what i want.

I have made an attachment to show you what i'm working with.


search data.xlsx

kind regards

[AliGW]

Is the data in the attachment real? If so, you might want to replace it with insensitive data.

[Tricke86]

no loooool

[AliGW]

no loooool

Not obvious. Just trying to protect your interests.

[oeldere]

@Tricke86

Please remove the confidential information from your sheet, and post it again.

@AliGW

Very good point

[Tricke86]

theres no confidential information its all fake

[oeldere]

@Tricke86

according to your file (which i tried to open in excel 2003) there are links with another workbook.

In that case i get the faillure "Name".

[Tricke86]

it might be that your using excel 2003, i am working from excel 2010 and 2007

the link i think your referring too is a hyperlink that takes me to "search" worksheet

[AliGW]

I can confirm that it opens without an error message in Excel 2013.

[Sanu2050]

hi


first you creat the index of you data which you want to search and then use match fucnction

[Tricke86]

Hi Sanu2050

thank your for your solution is it possible you could compile the formula for me please

Kind regards

[oeldere]

@Tricke86

Please don't PM me, but ask the question on the forum.

You halve links to other workbooks in your file?

[Tricke86]

here is the new workbook with no links

[oeldere]

Where do you want the answer.

Where do you look up the data.

Please add more informatio.

What is the desired result.

[Tricke86]

i want the answer to show up on the "search" sheet it brought up by vlookup on that sheet

the data is taken from "Data" sheet

the desired result is to search for two columns of data from one search source. AN example in C5 on the search tab i type "Admin" the results should bring up results that were found in department 1 and 2 on the data tab.

At the moment it only returns items that are found in both departments example employee Ross has Admin in department 1 and Admin in department 2. However, if employee Ryan has Admin in department 1 and R&D in department 2 it wont show a result.

Therefore i need a formula that can return results from either column of data My Logic is : If data not found in department 1 then search department 2.

[Tricke86]

Where do you want the answer.

Where do you look up the data.

Please add more informatio.

What is the desired result.

i want the answer to show up on the "search" sheet it brought up by vlookup on that sheet

the data is taken from "Data" sheet

the desired result is to search for two columns of data from one search source. AN example in C5 on the search tab i type "Admin" the results should bring up results that were found in department 1 and 2 on the data tab.

At the moment it only returns items that are found in both departments example employee Ross has Admin in department 1 and Admin in department 2. However, if employee Ryan has Admin in department 1 and R&D in department 2 it wont show a result.

Therefore i need a formula that can return results from either column of data My Logic is : If data not found in department 1 then search department 2.

[Sanu2050]

Hi

You have to use same formula in both coloumn formuls is

=index(select departmentcolumn ,match(select the name of employee, than select the column in which all name , 0,)

[sktneer]

Try this Array Formula in D10. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after pasting the formula in D10, press F2 (Function Key), hold down the Ctrl+Shift together and then press Enter.)

While C5 = "Admin", in D10

Please Login or Register  to view this content.

and then copy across and down.

Is this what you are trying to achieve?