Need Help ASAP .. PLEASE

  • Thread starter Thread starter Cathy
  • Start date Start date
C

Cathy

Can anyone can come up with formula to help me do the
following -

In cell AJ3 the formula would go to cell AJ2 and look up
the date and find the matching date in column U. Then it
would go to cell AD3 and get that number and match it up
in column X and give me the number that is in column Y.

The date and number can appear more than once and I need
all the numbers in column Y. The numbers that appear in
AJ3 can be separated by a space or whatever. Just as long
as all of them appear.

I really appreciate any assistance.

Cathy
 
Hi Cathy
can you post some example data (plain text - no attachments please)
with values for your columns and your expected results. I'm currently
not sure what you're looking up in column U
 
U3:U10Date X3:X10Home Y3:Y10Away AD3-Team# AJ2-3/6(date)
3/6/04 214 217 201 AJ3 need formula
3/6/04 201 222
3/6/04 202 255
3/6/04 201 206
3/6/04 224 223
3/6/04 202 205
3/7/04 211 217
3/13/04 201 207


The formula would look in AJ2 for a date and match it up
in column U. The it would look at cell AD3 and find every
time it is listed in column X. The it would give me the
numbers in Colum Y.

So in the example above 201 appears twice in Column Y with
a 3/6 date. So the numbers in AJ3 would be -

222 201

There can be spaces inbetween 222 and 201 or comma's. It
doesn't matter.

Again, I really appreciate any help. I am a real novice
at forumla's.

Cathy
 
Hi
using only Excel formulas: not possible -> this requires macros.
You might consider using Alan Beban's Arrays functions. It includes a
VLOOKUPS function which returns multiple lookup matches (haven't tested
it by myself though)
You'll find these functions at: http://home.pacbell.net/beban
 
Cathy said:
Can anyone can come up with formula to help me do the
following -

In cell AJ3 the formula would go to cell AJ2 and look up
the date and find the matching date in column U. Then it
would go to cell AD3 and get that number and match it up
in column X and give me the number that is in column Y.

The date and number can appear more than once and I need
all the numbers in column Y. The numbers that appear in
AJ3 can be separated by a space or whatever. Just as long
as all of them appear.

I really appreciate any assistance.

Cathy
Click to expand...


I think I can help you, but a sheet with the data would be helpful.
You can send your test data to my email address.

Striker
 
The VLookups function is designed, as is the built-in VLOOKUP function,
to find values in the single lefthand column of the table array; and
both are designed, without additional formulas, to return no more than 1
number to a cell.

I believe a similar question was asked and answered recently, so someone
might jump in; but perhaps the OP might clean up the request to increase
the chance that someone will. The illustration doesn't, for example,
have 201 appearing twice in Column Y, as was stated; and it isn't clear
what logic would return 222 and *201* to AJ3.

Alan Beban
 
Hi Alan
getting curious to use your function I came to the following possible
solution for the OP:

-----------------
I tried the following and this may work for the OP:
- download your array functions
- download the free add-in Morefunc.xll
(http://longre.free.fr/english/)

Now use the following formula:
=MCONCAT(VLOOKUPS(AJ2,U3:X10,4,0)," ")

this returns all values from column X for which column U equals the
value in AJ2

If you need a combined condition (column U + column X have to match a
value: AJ2 + AD3) try the following:
1. Use a helper column left to column U -> use column T
2. Enter the following formula in T3
=U3 & X3
3. copy this down to Z10
4. Now use the following formula:
=MCONCAT(VLOOKUPS(AJ2&AD3,T3:Y10,6,0)," ")
 
Hi Frank

The download add in is not working properly. The download
has errors in it. Any other alternative?

Cathy
 
Hi Cathy
what errors did you have (Tested it and it works smootly). Can you
explain your error message?
 
...
...
Now use the following formula:
=MCONCAT(VLOOKUPS(AJ2,U3:X10,4,0)," ")
Click to expand...
...

This could be accomplished with only MOREFUNC.XLL and built-in functions alone.

=TRIM(MCONCAT(IF(U3:U10=AJ12,X3:X10,"")," "))

where TBL refers to U3:X10. Wrap your formula inside TRIM as well, and it'd be
only 1 character less and involve the same number and nesting of function calls.
However, IF() will recalc much faster then VLOOKUPS. That has nothing to do with
Alan's implementation and everything to do with the Excel-VBA interface.
 
Harlan said:
...
..
..

This could be accomplished with only MOREFUNC.XLL and built-in
functions alone.

=TRIM(MCONCAT(IF(U3:U10=AJ12,X3:X10,"")," "))

where TBL refers to U3:X10. Wrap your formula inside TRIM as well,
and it'd be only 1 character less and involve the same number and
nesting of function calls. However, IF() will recalc much faster then
VLOOKUPS. That has nothing to do with Alan's implementation and
everything to do with the Excel-VBA interface.
Click to expand...

Hi Harlan
good point -> the less UDFs the better :-)

Frank
 
Back
Top