Re: vlookup

[T. Valko]

No.

It can be done with other formulas but you need to give us the details.

--
Biff
Microsoft Excel MVP

"dannyboy8" <dann...@discussions.microsoft.com> wrote in message
news:18BDA154-EA10-42F6...@microsoft.com...
> Is there a way to use vlookup to return the instance value beyond the 1st
> instance in the array?

[T. Valko]

Ok, so you want to return the value from column B that corresponds to the
2nd instance of "%,VGP" in column A *and* there are only 2 instances of
"%,VGP" in column A...

=LOOKUP(2,1/(A2:A100="%,VGP"),B2:B100)

--
Biff
Microsoft Excel MVP

"dannyboy8" <dann...@discussions.microsoft.com> wrote in message

news:D95BB4B4-9938-4C5B...@microsoft.com...
> OK, in column A there is 1 particular identifier that is used 2 times
> (%,VGP
> is the identifier) for different sources of income in column B, and I need
> to
> return only the second instance of "%,VGP". There are no other unique
> identifiers to work with in this case.

[dannyboy8]

Mike, thought I had this, but I am gettnig a #NUM return in the cell for the
below formula, if I am puling the info from a completely different worksheet
not located
in the same file, does that make a difference?

=INDEX('[G W Pd 12 P&L.xls]P&L'!$P:$P,LARGE(('[G W Pd 12
P&L.xls]P&L'!$B1:$B1000="rental of equipment")*ROW('[G W Pd 12
P&L.xls]P&L'!$B1:$B1000),COUNTIF('[G W Pd 12 P&L.xls]P&L'!$B1:$B1000,"rental
of equipment")+1-$E$47))

"Mike H" wrote:

> Hi,
>
> E1 & f1 are just 2 cell references I chose. Array enter the formula in
> whatever cell you want and change E1 (2 instances) & F1 in the formula to
> which ever cell references you want. Then E1 or its equivalent are the lookup
> value and F1 or equivalent are the occurence so:
>
> %,VGP in E1
> 2 in F1
>
> Finds the second occurrence of %,VGP in column A and returns the
> corresponding value from column B
>
> Mike
>
> "dannyboy8" wrote:
>
> > Sorry Mike, I am just a bit confused on the F1, can you use any cell
> > reference here? Do you literally enter in the #3 in F1 in this case to return
> > the 3rd occurrence?
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > In this sample formula you are looking for a value (E1) in column A and
> > > returning the corresponding value in column B. F1 should hold the occurrence
> > > so a 3 in F1 returns the 3rd occurrence
> > >
> > > =INDEX(B1:B13,LARGE((A1:A13=E1)*ROW(A1:A13),COUNTIF(A1:A13,E1)+1-F1))
> > >
> > >
> > > This is an array formula which must be entered by pressing CTRL+Shift+Enter
> > > 'and not just Enter. If you do it correctly then Excel will put curly brackets
> > > 'around the formula {}. You can't type these yourself. If you edit the formula
> > > 'you must enter it again with CTRL+Shift+Enter.
> > >
> > > Mike