Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 4th 05, 01:25 PM
Abhijeet
 
Posts: n/a
Default how do i get mutiple values using vlookup in excel, lookup value .

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 !

  #2   Report Post  
Old January 4th 05, 02:31 PM
Anthony Slater
 
Posts: n/a
Default

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:B12 500,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



"Abhijeet" wrote:

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 !

  #3   Report Post  
Old January 4th 05, 11:27 PM
Dave Peterson
 
Posts: n/a
Default

How about a User Defined Function?
This returns a list separated by commas to a single cell.
http://groups.google.co.uk/groups?th...apeXSPAM.co m


Abhijeet wrote:

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 !


--

Dave Peterson
  #4   Report Post  
Old May 19th 05, 03:07 AM
Ben
 
Posts: n/a
Default

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,SMA LL(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,SMA LL(IF(Data!$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$ H$16200),""),ROW()))))

Both Instances the Cell is blank.




--
Thanks,

Ben


"Anthony Slater" wrote:

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:B12 500,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



"Abhijeet" wrote:

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 !

  #5   Report Post  
Old May 19th 05, 04:30 AM
Biff
 
Posts: n/a
Default

Hi!

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

See my reply to your other post.

Biff

"Ben" wrote in message
...
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,SMA LL(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,SMA LL(IF(Data!$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$ H$16200),""),ROW()))))

Both Instances the Cell is blank.




--
Thanks,

Ben


"Anthony Slater" wrote:

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:B12 500,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



"Abhijeet" wrote:

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 !





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Missing values in Excel Line Chart mw55309 Charts and Charting in Excel 1 January 19th 05 12:10 PM
How do I stop excel replacing numerical values with the date? Becca C Excel Discussion (Misc queries) 2 December 21st 04 12:12 PM
in excel, how do I find which values doesn't have a pair? jackies_place Excel Discussion (Misc queries) 2 December 17th 04 05:43 PM
excel values from + to - jjshariff Excel Discussion (Misc queries) 2 December 2nd 04 09:08 PM
Excel crashing when typing VLOOKUP Nick Excel Discussion (Misc queries) 2 December 2nd 04 02:27 PM


All times are GMT +1. The time now is 10:37 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017