Prev: How do I write to a closed work book as a "register"
Next: how do i watermark an excel spreadsheet?
From: cv8497 on 27 May 2010 23:58
I am needing to look up data from a seperate report in which the VLOOKUP
formula will not work as there are multiple instances of the lookup value in
such. I would like to pull in specific results. Example - below is the
table_array in which I want to pull in the result where 'jim' is either "def"
or "jkl", first occurance of such is fine. is this possible with a formula?
I then want to pull in the corresponding number in column c. Any suggestions
are appreciated!

a b c
1 jim abc 123
2 jim def 456
3 andy ghi 789
4 jim jkl 101
5 jim mno 111

From: Jacob Skaria on 28 May 2010 00:48
Try one of these formulas...

'will sum up the values of c1:c5 with matching critieras...So if there is
only one instance then this would work
=SUMPRODUCT((A1:A5="jim")*(B1:B5="def")*C1:C5)

'will return the first matching instance whatsoever
=INDEX(C1:C5,SUMPRODUCT((A1:A5="jim")*(B1:B5="def")*ROW(C1:C5)))


--
Jacob (MVP - Excel)


"cv8497" wrote:

> I am needing to look up data from a seperate report in which the VLOOKUP
> formula will not work as there are multiple instances of the lookup value in
> such. I would like to pull in specific results. Example - below is the
> table_array in which I want to pull in the result where 'jim' is either "def"
> or "jkl", first occurance of such is fine. is this possible with a formula?
> I then want to pull in the corresponding number in column c. Any suggestions
> are appreciated!
>
> a b c
> 1 jim abc 123
> 2 jim def 456
> 3 andy ghi 789
> 4 jim jkl 101
> 5 jim mno 111
>
From: cv8497 on 28 May 2010 09:39
In the second formula below, would it be possible to nest an OR formula in
the 2nd part of the sumproduct formula?
=INDEX(C1:C5,SUMPRODUCT((A1:A5="jim")*(or(B1:B5="def",b1:b5=”jkl”))*ROW(C1:C5)))???

I have multiple criteria i am searching for and it only needs to be on of
them, but there may not be all of them, just a few of the criteria. Same
example below but say I wanted to look for "tuv", "wxy" or "def". it can be
any one of these. do i need to do an IF with numerous index formulas?

"Jacob Skaria" wrote:

> Try one of these formulas...
>
> 'will sum up the values of c1:c5 with matching critieras...So if there is
> only one instance then this would work
> =SUMPRODUCT((A1:A5="jim")*(B1:B5="def")*C1:C5)
>
> 'will return the first matching instance whatsoever
> =INDEX(C1:C5,SUMPRODUCT((A1:A5="jim")*(B1:B5="def")*ROW(C1:C5)))
>
>
> --
> Jacob (MVP - Excel)
>
>
> "cv8497" wrote:
>
> > I am needing to look up data from a seperate report in which the VLOOKUP
> > formula will not work as there are multiple instances of the lookup value in
> > such. I would like to pull in specific results. Example - below is the
> > table_array in which I want to pull in the result where 'jim' is either "def"
> > or "jkl", first occurance of such is fine. is this possible with a formula?
> > I then want to pull in the corresponding number in column c. Any suggestions
> > are appreciated!
> >
> > a b c
> > 1 jim abc 123
> > 2 jim def 456
> > 3 andy ghi 789
> > 4 jim jkl 101
> > 5 jim mno 111
> >
 | 
Pages: 1
Prev: How do I write to a closed work book as a "register"
Next: how do i watermark an excel spreadsheet?