Select value on a cell by matching value of two cells

M

maniarasan

I am in need of a macro to select the value based on two columns
value.

Below is the sample spreadsheet where my datas are arranged:

Column A Column B Column C
(City) (County)
(Factor)
ABBEVILLE HENRY 06
ABBEVILLE FD HENRY 06
ABERNANT CSA TUSCALOOSA 07
ABERNATHY CSA CLEBURNE 09
ADAMSBURG CSA DEKALB 05
ADAMSBURG CSA CHEROKEE 10
ADAMSVILLE JEFFERSON 01
ADDISON WINSTON 06
ADDISON FD WINSTON 06
ALTOONA ETOWAH 01
ALTOONA BLOUNT 07

For eg, for city "ALTOONA" and county "BLOUNT", the factor value
should return as "07". The factor value should return based on the
match of both A and B cell value.

Please help me by posting the macro for above scenario.

Thanks,
Maniarasan
 
M

ManicMiner17

I am in need of a macro to select the value based on two columns
value.

Below is the sample spreadsheet where my datas are arranged:

Column A Column B Column C
(City) (County)
(Factor)
ABBEVILLE HENRY 06
ABBEVILLE FD HENRY 06
ABERNANT CSA TUSCALOOSA 07
ABERNATHY CSA CLEBURNE 09
ADAMSBURG CSA DEKALB 05
ADAMSBURG CSA CHEROKEE 10
ADAMSVILLE JEFFERSON 01
ADDISON WINSTON 06
ADDISON FD WINSTON 06
ALTOONA ETOWAH 01
ALTOONA BLOUNT 07

For eg, for city "ALTOONA" and county "BLOUNT", the factor value
should return as "07". The factor value should return based on the
match of both A and B cell value.

Please help me by posting the macro for above scenario.

Thanks,
Maniarasan
Click to expand...

Not really sure that this is what you are looking for:

I've implemented a function to return the value in column C if a match
is found in columns A & B.
Note that only the first match is returned - based on your post I assume
that the matches in A & B are always unique.

Call the Function with

=Factor("String to find in first Column", "String to find in 2nd
Column", Range to search in First Column)

If there is no match zero is returned.

Function Factor(A As String, B As String, C As Range) As Variant
Dim D As Range

Application.Volatile True

For Each D In C
If D.Value = A Then
If D.Offset(0, 1) = B Then
Factor = D.Offset(0, 2)
Exit Function
End If
Else
Factor = 0
End If
Next D
End Function
 
Top