Compare cell against multiple columns

Solved/Closed
ExcelHelpless - Apr 19, 2010 at 10:16 AM
 ExcelHelpless - Apr 21, 2010 at 10:29 AM

Hopefully I can explain this as simply as possible. First here is what my spreadsheet looks like I have sepereated columns by |:

B | C| D| E| F| G
Title| Form| none| A020| A030| A040
1209| ???| -----| 221| 301| 350
308 | ???| -----| 300| 302| 353
----| ----| -----| 300a| 303 |
----| ----| -----| 1209 | 308 |

B is where I will imput data. If B = any of the data in Columns E,G, or F then C should = the first row in E,G, or F. So, in my example C2 = A020 and C3 = A030. Now, I have an If statement that will do this, but I have a total of 15 columns (E-S) and can only nest 7 if statements. Is there an easier way? I have provided the If statement I can use for the first 7 columns below. Thanks in advance for any help!

My formula:

=IF(COUNTIF($E$1:$E$7,B2),$E$1,IF(COUNTIF($F$1:$F$7,B2),$F$1,IF(COUNTIF($G$2:$G$3,B2),$G$1,IF(COUNTIF($H$2:$H$22,B2),$H$1,IF(COUNTIF($I$2:$I$6,B2),$I$1,IF(COUNTIF($J$2:$J$7,B2),$J$1,IF(COUNTIF($K$2:$K$3,B2),$K$1,"")))))))

5 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 19, 2010 at 09:33 PM
Very nifty trick of using count to locate the value. However you are right about hitting the limit of 7 nested if. It is time for you to go to macro world

Steps
1. Press ALT + F11 to enter VBE environment
2. Click on insert, and insert a new module
3. Paste the code given below
4. Use the function in your column. Example of usage is =FindHeader(B2,E:S )

Function FindHeader(LookForValue As Variant, LookInRange As Range) As Variant 
' PURPOSE:  To find a value in a given range and if found return the value 
'           found in first row of the the column in which the value exists. 
'           If value is not found then "" is returned 
'CALL:      =FindHeader(B2,E:S ) 

Dim rFound As Range ' where the value being searched found 
Dim firstCellAddress As String 'the address of the first cell in the range being looked 
Dim cell As Range ' cell of first 

    'The first part of the range being looked 
    firstCellAddress = Left(LookInRange.Address, InStr(1, LookInRange.Address, ":") - 1) 
         
    ' making sure if the last character is a number. If it is not a number then range is of 
    ' sort A:Z instead of A1:Z50 
    If Not (IsNumeric(Right(firstCellAddress, 1))) Then 
         
        firstCellAddress = firstCellAddress & 1 
     
    End If 
     
    ' looking for the value 
    Set rFound = LookInRange.Find( _ 
                            What:=LookForValue, _ 
                            After:=Range(firstCellAddress), _ 
                            LookIn:=xlFormulas, _ 
                            LookAt:=xlWhole, _ 
                            SearchOrder:=xlByRows, _ 
                            SearchDirection:=xlNext, _ 
                            MatchCase:=False, _ 
                            SearchFormat:=False) 
     
    If (rFound Is Nothing) Then 
         
        FindHeader = "" 
     
    Else 
     
        FindHeader = Cells(1, rFound.Column) 
         
    End If 

    Set rFound = Nothing

End Function 
1
ExcelHelpless
Apr 20, 2010 at 08:13 AM
Rizvisa1,

Great job! I can't begin to understand everything that is going on in this macro, but it is working very good. There is only one issue, if the formula is put in coulmn C without any data in B2, column C shows the header for column L.

To try and fix this, I did the following:
I have used =FindHeader(B2:$E:$T) (this adds a blank column, which now returns 0 if there is no data in B2, and will work just fine for me).

Question answered! Thank you very much!!!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 20, 2010 at 09:04 AM
You can call the function like this to handle that issue.

=IF(ISBLANK(B2), "",FindHeader(B2,$E:$T))

which says , if B2 is blank then show blank, else use the function
0
ExcelHelpless
Apr 21, 2010 at 10:29 AM
Excellent. Thanks again!!!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 19, 2010 at 10:27 AM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
0
ExcelHelpless
Apr 19, 2010 at 10:35 AM
I will try this evening. Company web filter blocks upload/download sites:(
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 19, 2010 at 10:50 AM
See this
https://ccm.net/forum/affich-308205-match-with-conditional-format-in-excel-2003

Your problem seem to be very close to this issue
0

Didn't find the answer you are looking for?

Ask a question
ExcelHelpless
Apr 19, 2010 at 01:52 PM
It does seem to be close, unfortunately, I can't seem to get it to work...
0