Help consolidate into 1 formula

[Fyrsign]

Anyone want to take a shot at helping me consolidate these 7 formula
into just one, so that I can apply it to a single column rather tha
have 7 seperate columns with results in them?

=IF(ISNA(VLOOKUP(A2,$B$2:$B$350,1,FALSE)),"","Canada")
=IF(ISNA(VLOOKUP(A2,$C$2:$C$185,1,FALSE)),"","Central")
=IF(ISNA(VLOOKUP(A2,$D$2:$D$24,1,FALSE)),"","Dealer")
=IF(ISNA(VLOOKUP(A2,$E$2:$E$196,1,FALSE)),"","East")
=IF(ISNA(VLOOKUP(A2,$F$2:$F$186,1,FALSE)),"","Mid")
=IF(ISNA(VLOOKUP(A2,$G$2:$G$120,1,FALSE)),"","South")
=IF(ISNA(VLOOKUP(A2,$H$2:$H$154,1,FALSE)),"","West"

 

[Frank Kabel]

Hi
any chance that in row 1 the region is stored?. Also how do you want
these formulas combined?

 

[Steve Smallman]

Fyrsign,

I tried to compile it into one formula but it got a bit complex, and on the
seventh nested If, rejected the vlookup for some reason. I'm not sure why,
but perhaps it was string length.

Try going into the VB project (Tools\Macro\Visual Basic Editor), inserting a
module and pasting this code in the module.

Function Look_for(lookup_Value As String, lookup_range As Range)

Look_for = "not found"
For Each cell In lookup_range
If cell.Value = lookup_Value Then
Select Case cell.Column
Case 2
Look_for = "Canada"
Case 3
Look_for = "Central"
Case 4
Look_for = "Dealer"
End Select
Exit For
End If
Next cell


End Function


Add to the cases to include all of your options.

What this does is create a user defined function. This is a little nbit of
Visual Basic for Applications (VBA) code to achieve a desired result.

The code asks for two variable, a reference to a value to look for and a
range to look in.

It then tests each cell in the range to find the value it is looking for, if
it finds the value, it then needs to determine what to return. this is done
by testing the column in which the value is found. Column A returns a value
of 1, Column E a value of 5. When selecting the range to look in, select the
whole range, it ignores blanks UNLESS the lookup value is a blank. In which
case it will return the value associated with the first column in the range
which contains a blank cell

Steve