Lookup-type macro?

  • Thread starter Thread starter Khun John
  • Start date Start date
K

Khun John

Hi
Are there any macros or functions, similar to LOOKUP, that can capture all
matches rather than the first match? I've created a livestock spreadsheet
but want to add a Diary Planner worksheet to it that will indicate
activities requiring attention on any given day. For example, it could
indicate that two cows (referring to their ID#) are due to be
pregnancy-checked (100 days after insemination). The ID numbers and 100-day
palpitation dates are already on another worksheet (referred to as "Input"
in my earlier message below).
Any ideas?
Thanks
John
Subject: INDEX, OFFSET,..??
Click to expand...
Hi
I want to create a worksheet "Diary" to act as a diary planner. In worksheet
"Diary", Column A will be the date series. Diary B1 will lookup a table
($A$1:$O$500) in worksheet "Input" for the ID# (Input Column A) for any date
in Column O that matches the date in Diary A1. There may be more than one
matching date therefore each corresponding ID# would be required.
For example where Diary A1=21-07-2003 and Input O23 & O106 also =21-07-2003,
Diary B2 should return the values (ID#) of both A23 & A106 separated by a
comma (A23&","&A106).
The values in Input Column A (ID#) are in ascending order but the dates in
Input Column O are necessarily not in order; there are also date values in
some other columns between Input Column A and Input Column O.
Can anyone help?
Thanks
John
 
Thanks Alan. I've yet to try it but your description of its functionality is
exactly what I'm looking for.
Kind regards
John
 
Hi Alan

I expect I must be doing something wrong. The Vlookups function is only
returning the first value.

Diary!A57=24-02-2003
Diary!B57=ArrayFunctions.xls!VLookupLeft(A57,Input!$A$3:$O$502,0,15)
15thColumn in the array has two occurrences of the value 24-02-2003 (in
Input!O13 & 14)
Input!A13=11; Input!A14=12
Diary!B57 should return "11, 12" but is only returning "11", ie the first
matched value.

Can you please point out my error?
Thanks
John
 
Hi Alan et al

I've now realised that I used VLookupLeft rather than VLookups. I've
simplified the array into two columns: 1st column (Input!FL) contains the
variable dates, 2nd column (Input!FM) contains the unique ID numbers.
Diary!B57=ArrayFunctions.xls!VLookups(A57,Input!$FL$3:$FM$502,2).
Diary!A57=24-02-03
Since Input!FL13 & 14 both=24-02-03 (ie both match the lookup value),
Diary!B57 should return "11, 12" (since Input!FL13=11 and Input!FM=12).
I'm getting the #VALUE! instead.

I found your response to a similar board question back in 2001
(http://groups.google.com/groups?q=VBA+vlookups+multiple&hl=en&lr=&ie=UTF-8&
oe=UTF-8&selm=%23jMq4%24O1AHA.1392%40tkmsftngp02&rnum=3). I tried the same
example in the same layout but the functions (both ColumnVector and
VLookups) returned only the first match for me. I'm using Excel 97 Windows
98.

Any ideas?
Thanks
John
 
Back
Top