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 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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.

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

' 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

End If

' looking for the value
Set rFound = LookInRange.Find( _
What:=LookForValue, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If (rFound Is Nothing) Then

Else

End If

Set rFound = Nothing

End Function
```
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!!!
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 20, 2010 at 09:04 AM
You can call the function like this to handle that issue.

which says , if B2 is blank then show blank, else use the function
ExcelHelpless
Apr 21, 2010 at 10:29 AM
Excellent. Thanks again!!!
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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.
ExcelHelpless
Apr 19, 2010 at 10:35 AM