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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 !
 
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
 
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.
 
Hi!

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

See my reply to your other post.

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top