how do i get mutiple values using vlookup in excel, lookup value .

[Guest]

hi , i have data stored in excel as column a- Purchase order no column b-
Invoice no i want to query basis purchase order no & result should give
mutiple invoice no stored agst one purchase order no how do i do this using
lookup or something !

 

[Guest]

I use the following for something similar. It might be what you are looking
for: -

=IF(ISERROR(INDEX(B1:B12500,SMALL(IF(A1:A12500=D1,ROW(B1:B12500),""),ROW()))=FALSE),"",(INDEX(B1:B12500,SMALL(IF(A1:A12500=D1,ROW(B1:B12500),""),ROW()))))

Enter as Array Formula CTRL+SHIFT+ENTER

Copy this down as many rows as you think there are unique numbers (say 100
rows)

Column A would be your Invoice Numbers
Column B would be your Purchase order Numbers
D1 would be the Purchase order number you are searching for

 

[Dave Peterson]

How about a User Defined Function?
This returns a list separated by commas to a single cell.
http://groups.google.co.uk/[email protected]

 

[Guest]

Anthony,

I tried to use your formula for my application but it is not working what am
I missing?

The formula I am using is
=IF(ISERROR(INDEX(Data!$E$1:$E$16200,SMALL(IF(Data!$H$1:$H$16200=$E$5&$B11,ROW(Data!$E$1:$E$16200),""),ROW()))=FALSE),"",(INDEX(Data!$E$1:$E$16200,SMALL(IF(Data!$H$1:$H$16200=$E$5&$B11,ROW(Data!$E$1:$E$16200),""),ROW()))))

$E$5&$B11= The JOb#_Phase Type I am looking for.
Data!$E$1:$E$16 = The range where the Job#_Phase Type are found multiple
times.
Data!$H$1:$H$16200=The range where the dates are found or the answers I want
returned.

I also tried
=IF(ISERROR(INDEX(Data!$H$1:$H$16200,SMALL(IF(Data!$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$H$16200),""),ROW()))=FALSE),"",(INDEX(Data!$H$1:$H$16200,SMALL(IF(Data!$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$H$16200),""),ROW()))))

Both Instances the Cell is blank.

 

[Biff]

Hi!

This (these) formulas are dependant upon which row they're in:
......ROW()))))

See my reply to your other post.

Biff