Compare cell against multiple columns [Solved/Closed]

Report
-
 ExcelHelpless -

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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month


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!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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

Excellent. Thanks again!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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.

I will try this evening. Company web filter blocks upload/download sites:(
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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

It does seem to be close, unfortunately, I can't seem to get it to work...

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!