"L" plate driver needs guidance

[Rodney]

Hi all,

Is it possible in a VLookup situation
to extract the "second" occurance of a match rather than the first?

I extract details of a first match, from a list of names/expenditure
lets say a customer spent $1
now I want to find out how much the customer spent on
a previous visit, other than that matched lookup.

the customer / expense is in a table 61,000 records long

Thank you.

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))

Note: If you really have >60K records you may consider using something
else than Excel (e.g. a real database)

 

[Rodney]

Thank you Frank.

| Note: If you really have >60K records you may consider using something
| else than Excel (e.g. a real database)


"if you really..........."
What! do people tell lies on this NG?

Actually I have 4 million records in VFP, but I dump a sub set down
to excel for "what if" scenarios. Excel will only take 65,000
so I need some space for the reference list.

I am unable to visualise how using a database could sort my queries
In fact I cross over from database to do the lookup.
How else could I refer say 800 names in a lookup scenario in a database.

I <do> appreciate your response

Rod

 

[Rodney]

I was unable to manufacture a result, based on your
suggestion Frank.
My current formula reads:

=IF(ISERROR(VLOOKUP(B61726,$B$2:$E$61721,4,FALSE)),"/",(VLOOKUP(B61726,$B$2:$E$61721,4,FALSE)))

Are you able to suggest anything based on that please?





| Hi
| try the array formula (entered with CTRL+SHIFT+ENTER):
| =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
|
| Note: If you really have >60K records you may consider using something
| else than Excel (e.g. a real database)
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany

 

[Frank Kabel]

Hi
for this many data records Excel is definetely the wrong tool (at least
IMHO). For this consider using specialised tools. e.g.
- for Reporting: Crystal Enterprise or Business Objects
- For data mining specialised data mining tools
- as you're talking about customer data you may consider using a CRM
package. e.g. mySAP CRM, Siebel, Peoplesoft

Frank
P.S.: Don't know what VFP is )

 

[Frank Kabel]

Hi
this should work. What does not work exactly?

 

[Rodney]

Oh!.... I'll have another try (read bumble) Frank,
VFP=Visual Fox Pro Database.

Thankyou.




| Hi
| this should work. What does not work exactly?
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany
|
| | > I was unable to manufacture a result, based on your
| > suggestion Frank.
| > My current formula reads:
| >
| >
| =IF(ISERROR(VLOOKUP(B61726,$B$2:$E$61721,4,FALSE)),"/",(VLOOKUP(B61726,
| $B$2:$E$61721,4,FALSE)))
| >
| > Are you able to suggest anything based on that please?
| >
| >
| >
| >
| >
| > | Hi
| > | try the array formula (entered with CTRL+SHIFT+ENTER):
| > |
| =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
| > |
| > | Note: If you really have >60K records you may consider using
| something
| > | else than Excel (e.g. a real database)
| > |
| > | --
| > | Regards
| > | Frank Kabel
| > | Frankfurt, Germany
| >
| >
| >
|