Indexing into Vlookup

[Twinson]

I am trying to automate an invoice where I can lookup a data table using
a customer code, and then drop each item the customer purchases into
the inoice.

I can use the vlookup function to return a single value, which is the
first item the customer purchases, but can't work out how to return the
next correct match (customer purchase) in the data list following on
from the last match returned.

Hope this makes sense??!??

Thanks in advance

 

[Alan Beban]

One thing you might want to consider is the VLookups function in the
freely downloadable file at http://home.pacbell.net/beban

Alan Beban

 

[Twinson]

Thanks for the tip but that function doesn't seem to have the magi
'index number' argument that would allow me to say give me the secon
or third etc occurance of this match. Any other ideas???? Thank
again

 

[Arvi Laanemets]

Hi

I copied here my answer to some Excel NG thread from past month. Try this
out with some dummy workbook - I belive you get some ideas.
***
An example:

Create a workbook with 3 sheets - Sales, Names and Report

Into Names worksheet enter the list of names, with heading in A1
Define the named range Name
=OFFSET(Names!$A$2,,,COUNTIF(Names!$A:$A,"<>")-1,1)

On sheet Sales create a table with columns
ActiveRow, RowRank, Name, Date, Sales
When you want, then you can format some range in column C (Name), using
Data.Validation.List (=Name), so you can select names from dropdown.
Define named range Sales
=OFFSET(Sales!$B$2,,,COUNTIF(Sales!$C:$C,">""")-1,4)

On sheet Report into range A1:A3 enter: "Name:", "Month:", "Year:"
Format cell B1 using Data.Validation.List (=Name)
Format cell B2 using Data.Validation.List
("January","February",...,"December")
NB! Fill the gap in list above, so all 12 months are included!
Format cell B3 using Data.Validation.List (2003,2004)
Define named range RepName
=Report!$B$1
Define named range RepMonth
=IF(Report!$B$2="",0,MATCH(Report!$B$2,{"January","February",...,"December"}
;0))
NB! Fill the gap in formula above, so all 12 months are included!
Define named range RepYear
=Report!$B$3

On sheet Sales, select any cell in row 2 and create named range Selected
=IF(OR(OR(Sales!$C2="",Sales!$C2<>RepName),AND(RepYear<>"",YEAR(Sales!$D2)<>
RepYear),AND(RepMonth>0,MONTH(Sales!$D2)<>RepMonth)),"",ROW())
Into A2 enter the formula
=Selected
Into B2 enter the formula
=IF(A2="","",RANK(A2,$A:$A,1))
Copy the range A2:B2 down as much rows as you think you'll need
Hide columns A:B
Enter your sales table, when it didn't exist earlier.

On sheet Report, into some row, p.e. row 5, enter header row, p.e. Nr, Date,
Sales
Into cell A6 enter the formula
=IF(ISERROR(VLOOKUP(ROW(A6)-ROW(A$5),Sales,1,FALSE)),"",VLOOKUP(ROW(A6)-ROW(
A$5),Sales,1,FALSE))
Into B6
=IF($A6="","",VLOOKUP($A6,Sales,3,FALSE))
Into C6
=IF($A6="","",VLOOKUP($A6,Sales,4,FALSE))
Copy range A6:C6 down as much as you think you'll need.
Create a total field for selected sales on passable location.

It's done!
***

 

[Alan Beban]

=INDEX(VLookups(lookup_value,A1:C100,3),n,0) will return the value from
Column C corresponding to the nth occurrence of lookup_value in Column
A,assuming that there are at least n occurrences--otherwise it retruns #REF!

Alan Beban

 

[Twinson]

Thanks Alan, the formula worked an absolute treat! I've now been
able to fully automate the invoicing for my friend, which has greatly
optimised his front office needs.

One last question. How do I include your formulas / module into his
Excel file so as the formula doesn't link from your ArrayFunctions
file?

 

[Alan Beban]

I'm not sure what you mean by "so as the formula doesn't link from your
ArrayFunctions file"; but I think that one way to do what you ask is to
simply paste the two functions, VLookups and ArrayCountIf, into a
general module in his Excel workbook.

Alan Beban

 

[Twinson]

Thanks again, I've pasted the two functions.

Do you have a vlookupsleft that allows you to use the index function a
with vlookups

 

[Alan Beban]

No; the VLookupLeft function, like the built-in VLOOKUP function, only
searches for the first occurrence of the lookup value.

Perhaps Chip Pearson's page that includes descriptions of some arbitrary
lookups might be of help--I don't recall.

Alan Beban