I have a spreadsheet with 2 sheets. Sheet 1 column A has a list of servers. Sheet 2 has 5 columns (A-E). I want to compare what is in Sheet 1 Column A to the Columns on sheet 2. If there is a match of Column A Sheet 1 and Column A sheet 2, then return POK1. If there is a match between Column A Sheet 1 with Column B sheet 2, then return POK2. If there is a match between Column A Sheet 1 with Column C sheet 2, then return POK3. If there is a match between Column A Sheet 1 with Column D sheet 2, then return POK4. If there is a match between Column A Sheet 1 with Column E sheet 2, then return POK5.
Any help is very much appreciated.
How about
="POK"&IF(COUNTIF(Sheet2!A:A,A2),1,IF(COUNTIF(Sheet2!B:B,A2),2,IF(COUNTIF(Sheet2!C:C,A2),3,IF(COUNTI F(Sheet2!D:D,A2),4,IF(COUNTIF(Sheet2!E:E,A2),5," not found")))))
For the first value in sheet1, cell A2, put this formula in B2:
=IF(ISNUMBER(MATCH($A2,Sheet2!A:A,0)), "POK1", IF(ISNUMBER(MATCH($A2,Sheet2!B:B,0)), "POK2", IF(ISNUMBER(MATCH($A2,Sheet2!C:C,0)), "POK3", IF(ISNUMBER(MATCH($A2,Sheet2!D:D,0)), "POK4", IF(ISNUMBER(MATCH($A2,Sheet2!E:E,0)), "POK5", "No Match")))))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Originally Posted by JBeaucaire
Thanks. Seems to work well.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks