lookup multiple occurrences of a value excel

Hi,

can anyone suggest a way to lookup multiple occurrences of a value in excel
and return a value from a cell from a cell in the same row of each
occurrence. I want the values returned to each be in a separate cell.

thanks

e.g.

I want to put a lookup formula in columns e, f and g of row 2 in wkbk 1 that
would look at the isbn in a1, find it in wkbk 2, then return the first
occurrence 'po' for that isbn to e1, the second occurrence to f1 and the
third occurrence to g1.
can anyone help??

workbook 1
a b c d e f g
1 isbn title price code
2 1234 dog 500 1
3 5678 cat 100 2
4 9101 rat 100 3
5 1121 pig 300 4

workbook 2
a b
1 isbn po
2 1234 abc
3 1234 xyz
4 5678 def
5 1121 xyz
6 1121 hij

Try the following...

E2, copied down and across:

=IF(COLUMN()-COLUMN($E2)+1<=COUNTIF(Sheet2!$A$2:$A$6,Sheet1!$A2),INDEX(Sh
eet2!$B$2:$B$6,SMALL(IF(Sheet2!$A$2:$A$6=Sheet1!$A2,ROW(Sheet2!$A$2:$A$6)
-CELL("row",Sheet2!$A$2)+1),COLUMN()-COLUMN($E2)+1)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
ckl <[email protected]> wrote:

> Hi,
>
> can anyone suggest a way to lookup multiple occurrences of a value in excel
> and return a value from a cell from a cell in the same row of each
> occurrence. I want the values returned to each be in a separate cell.
>
> thanks
>
> e.g.
>
> I want to put a lookup formula in columns e, f and g of row 2 in wkbk 1 that
> would look at the isbn in a1, find it in wkbk 2, then return the first
> occurrence 'po' for that isbn to e1, the second occurrence to f1 and the
> third occurrence to g1.
> can anyone help??
>
> workbook 1
> a b c d e f g
> 1 isbn title price code
> 2 1234 dog 500 1
> 3 5678 cat 100 2
> 4 9101 rat 100 3
> 5 1121 pig 300 4
>
> workbook 2
> a b
> 1 isbn po
> 2 1234 abc
> 3 1234 xyz
> 4 5678 def
> 5 1121 xyz
> 6 1121 hij
>

Try this *array* formula in E2.

=INDEX(Sheet2!$B$1:$B$20,SMALL(IF(Sheet2!$A$1:$A$20=$A2,ROW($A$1:$A$20),""),
COLUMN(A1)))

Array formulas are entered with <Ctrl> <Shift> <Enter>, where, when done
correctly, will *automatically* be enclosed in curly brackets, which
*cannot* be done manually.

Then, just drag aross and down.

If no (ISBN) match is found, or there are less then 3 P.O.'s, the #NUM!
error will display.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"ckl" <[email protected]> wrote in message
news:[email protected]...
Hi,

can anyone suggest a way to lookup multiple occurrences of a value in excel
and return a value from a cell from a cell in the same row of each
occurrence. I want the values returned to each be in a separate cell.

thanks

e.g.

I want to put a lookup formula in columns e, f and g of row 2 in wkbk 1 that
would look at the isbn in a1, find it in wkbk 2, then return the first
occurrence 'po' for that isbn to e1, the second occurrence to f1 and the
third occurrence to g1.
can anyone help??

workbook 1
a b c d e f g
1 isbn title price code
2 1234 dog 500 1
3 5678 cat 100 2
4 9101 rat 100 3
5 1121 pig 300 4

workbook 2
a b
1 isbn po
2 1234 abc
3 1234 xyz
4 5678 def
5 1121 xyz
6 1121 hij

Thank you Domenic and RagDyer....will try both.

"ckl" wrote:

> Hi,
>
> can anyone suggest a way to lookup multiple occurrences of a value in excel
> and return a value from a cell from a cell in the same row of each
> occurrence. I want the values returned to each be in a separate cell.
>
> thanks
>
> e.g.
>
> I want to put a lookup formula in columns e, f and g of row 2 in wkbk 1 that
> would look at the isbn in a1, find it in wkbk 2, then return the first
> occurrence 'po' for that isbn to e1, the second occurrence to f1 and the
> third occurrence to g1.
> can anyone help??
>
> workbook 1
> a b c d e f g
> 1 isbn title price code
> 2 1234 dog 500 1
> 3 5678 cat 100 2
> 4 9101 rat 100 3
> 5 1121 pig 300 4
>
> workbook 2
> a b
> 1 isbn po
> 2 1234 abc
> 3 1234 xyz
> 4 5678 def
> 5 1121 xyz
> 6 1121 hij
>
>

Hi Again RagDyer,

it worked. Thank you very much!

"RagDyer" wrote:

> Try this *array* formula in E2.
>
> =INDEX(Sheet2!$B$1:$B$20,SMALL(IF(Sheet2!$A$1:$A$20=$A2,ROW($A$1:$A$20),""),
> COLUMN(A1)))
>
> Array formulas are entered with <Ctrl> <Shift> <Enter>, where, when done
> correctly, will *automatically* be enclosed in curly brackets, which
> *cannot* be done manually.
>
> Then, just drag aross and down.
>
> If no (ISBN) match is found, or there are less then 3 P.O.'s, the #NUM!
> error will display.
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "ckl" <[email protected]> wrote in message
> news:[email protected]...
> Hi,
>
> can anyone suggest a way to lookup multiple occurrences of a value in excel
> and return a value from a cell from a cell in the same row of each
> occurrence. I want the values returned to each be in a separate cell.
>
> thanks
>
> e.g.
>
> I want to put a lookup formula in columns e, f and g of row 2 in wkbk 1 that
> would look at the isbn in a1, find it in wkbk 2, then return the first
> occurrence 'po' for that isbn to e1, the second occurrence to f1 and the
> third occurrence to g1.
> can anyone help??
>
> workbook 1
> a b c d e f g
> 1 isbn title price code
> 2 1234 dog 500 1
> 3 5678 cat 100 2
> 4 9101 rat 100 3
> 5 1121 pig 300 4
>
> workbook 2
> a b
> 1 isbn po
> 2 1234 abc
> 3 1234 xyz
> 4 5678 def
> 5 1121 xyz
> 6 1121 hij
>
>
>

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ckl" <[email protected]> wrote in message
news:[email protected]...
> Hi Again RagDyer,
>
> it worked. Thank you very much!
>
> "RagDyer" wrote:
>
> > Try this *array* formula in E2.
> >
> >
=INDEX(Sheet2!$B$1:$B$20,SMALL(IF(Sheet2!$A$1:$A$20=$A2,ROW($A$1:$A$20),""),
> > COLUMN(A1)))
> >
> > Array formulas are entered with <Ctrl> <Shift> <Enter>, where, when done
> > correctly, will *automatically* be enclosed in curly brackets, which
> > *cannot* be done manually.
> >
> > Then, just drag aross and down.
> >
> > If no (ISBN) match is found, or there are less then 3 P.O.'s, the #NUM!
> > error will display.
> > --
> >
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> > "ckl" <[email protected]> wrote in message
> > news:[email protected]...
> > Hi,
> >
> > can anyone suggest a way to lookup multiple occurrences of a value in
excel
> > and return a value from a cell from a cell in the same row of each
> > occurrence. I want the values returned to each be in a separate cell.
> >
> > thanks
> >
> > e.g.
> >
> > I want to put a lookup formula in columns e, f and g of row 2 in wkbk 1
that
> > would look at the isbn in a1, find it in wkbk 2, then return the first
> > occurrence 'po' for that isbn to e1, the second occurrence to f1 and
the
> > third occurrence to g1.
> > can anyone help??
> >
> > workbook 1
> > a b c d e f g
> > 1 isbn title price code
> > 2 1234 dog 500 1
> > 3 5678 cat 100 2
> > 4 9101 rat 100 3
> > 5 1121 pig 300 4
> >
> > workbook 2
> > a b
> > 1 isbn po
> > 2 1234 abc
> > 3 1234 xyz
> > 4 5678 def
> > 5 1121 xyz
> > 6 1121 hij
> >
> >
> >