Vlookup in multiple arrays

[Guest]

I am trying to lookup values from a work book that has the same worksheet for
different months. The first column of these worksheets contains an
identifying number. I named the first column of these sheets the month name
and the full lookup array the month with the number 1 on the end. I used the
following array formula to lookup the value in column three of the different
lookup arrays for a cell D2 containing an identifiable number.
{=IF(OR(D2=JANUARY),VLOOKUP(D2, JANUARY1,3, FALSE), IF(OR(D2=FEBRUARY),
VLOOKUP(D2, FEBRUARY1,3,FALSE), IF(OR(D2=MARCH),VLOOKUP(D2, MARCH1,3,FALSE),
"NOT VALID")))}

I would like to have this formula hold true for the entire year, but I can
only nest 7 if statements. I think I need to write a select case custom
function, but I do not know the syntax to compare the function value to the
different months. Here is a code example of what I want to do.

FUNCTION SPECLOOKUP(ID#)
SELECT CASE
CASE ID# IS WITHIN JANUARY
SPECLOOKUP = WORKSHEETFUNCTION.VLOOKUP(ID#,JANUARY1,3,FALSE)
AND SO ON.
END SELECT
END FUNCTION
Can someone tell me how to write the cases correctly?

 

[Dave Peterson]

This can be done with a UDF, but it might be possible with a worksheet formula
(not by me, though!).

You may want to post in .worksheet.functions to see if those smart people can
help.

Because you won't be passing all the ranges to the UDF, excel won't recalculate
if you change one of the values in your month1 (january1, ..., december1)
tables.

Heck, I guess you could pass those along with the idnumber. It would just make
the formula longer.

I didn't use the first column range names (January, February, ..., December). I
just did the =vlookup() until there wasn't an error.

Option Explicit
Function mySpecLookup(idNumber As Variant) As Variant

Application.Volatile True

Dim myLookupRng As Range
Dim myMonthCol As Range
Dim iCtr As Long
Dim testRng As Range
Dim res As Variant

For iCtr = 1 To 12
Set testRng = Nothing
On Error Resume Next
Set testRng _
= ThisWorkbook.Names _
(Format(DateSerial(2005, iCtr, 1), "mmmm") & "1").RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'naming error --just skip???
Else
res = Application.VLookup(idNumber, testRng, 3, False)
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

mySpecLookup = res

End Function

And you'd use the function like:
=myspeclookup(D2)

But you could change the function to pass it those 12 ranges:

Function mySpecLookup(idNumber As Variant, rng1 as range, rng2 as range, _
rng3 as range, ......, rng12 as range) As Variant

=myspeclookup(D2,january1,february1,...,december1)

(You can't use the ellipses--you'll have to do all that typing!)