Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: multi value vlookup


(!)

spooky1's Avatar
spooky1 spooky1 is offline
Member with 126 posts.
THREAD STARTER
 
Join Date: Jan 2010
17-Jun-2012, 06:56 AM #1
Solved: multi value vlookup
I tried and I failed. see spreadsheet pls and if someone could point out the problem. Copied evrytink from MS website but it did not work.
Attached Files
File Type: xls Copy of MultiVlookups.xls (31.0 KB, 58 views)
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
17-Jun-2012, 07:06 AM #2
What is the problem.
The formula is corect, it shows the smalles value for 'ash' that's exactly what the format does.

If you change SMALL to LARGE it will show the largest value
I re attached your file with both options
So there is nothing wrong with the formula but I think your interpretation of it.
What is it you're looking for?
Attached Files
File Type: xls Copy of MultiVlookups.xls (33.5 KB, 39 views)
spooky1's Avatar
spooky1 spooky1 is offline
Member with 126 posts.
THREAD STARTER
 
Join Date: Jan 2010
17-Jun-2012, 05:53 PM #3
Multi value result usin vlookup
The title of the article on the MS website is:-
How to look up a value in a list and return multiple corresponding values

Okay, have mis understood, I assumed that multi value result meant showing more than one result like highlighted in bold at the bottom of this message .

What I want to do is show more than one value from col B, in example xls, in a cell.
Like ash is listed 2wice in Col A & has two differnt values for each in col B.

Is it possible to show lots of values in a cell if ash is in col A lots of times? If not lots then at least 3 values from col B?

Below copied from MS site
Return corresponding values from column B

Now, modify the preceding formula to return the corresponding value (and not the row number) and enter the formula as an array formula (CTRL+SHIFT+ENTER):
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2) The first corresponding value is 234, the value that corresponds to the first occurrence of the name "Ashish".
When you enter or fill this formula in subsequent cells, the formula returns the subsequent corresponding values of 534 and 834.


end



Just had something dropped on my head and saw a flash of light. Combine with & the lower value formula/code with the upper one listed on MS site? Would only give two values. Is there a mid function? If so I please provide code as I'm not smart enough to work it out. I'm very good at copy and paste though!


Perhaps there is another way to show many values if ash is entered in many times?
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
18-Jun-2012, 12:48 AM #4
If it is just marking all the occurrences of ash you could do this using a conditional format based for example on the input value in a cell.
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
18-Jun-2012, 02:11 AM #5
I have tried something using conditional format but do not know if it works with Excel 2003
You might get compatability issues
Attached Files
File Type: xls Multi-show using conditional format.xls (38.0 KB, 20 views)
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
18-Jun-2012, 02:17 AM #6
if you copy your formula to the next cell below, it will show 3333.
If you want to show everything in one cell, you need a custom function.
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
18-Jun-2012, 02:21 AM #7
Yeah, but the porster wants to see all the occurrences of the cell containing a name or part of a name. (see #3)
spooky1's Avatar
spooky1 spooky1 is offline
Member with 126 posts.
THREAD STARTER
 
Join Date: Jan 2010
18-Jun-2012, 02:35 AM #8
In a list with many other names etc

col A col b
ash 6
ash 10
ash 11
result in one cell or msgbox `6 10 11' and more if there are any
is what I was looking for, clues anyone? Impossible?

That was interesting and I suppose I could use it by copying it one cell down in lieu of anything else
Thanks Garf and Keballah
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
18-Jun-2012, 05:40 AM #9
Not with a formula but you can di this with a macro

Last edited by Keebellah; 18-Jun-2012 at 05:40 AM.. Reason: Type-Oooo
spooky1's Avatar
spooky1 spooky1 is offline
Member with 126 posts.
THREAD STARTER
 
Join Date: Jan 2010
18-Jun-2012, 05:57 AM #10
Macro Ok
Hi Keballa,
doing it with a macro is fine, where do I find such a macro?
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
18-Jun-2012, 07:05 AM #11
You write it!!!
Attached Files
File Type: xls Multi-show using macro.xls (48.5 KB, 35 views)
spooky1's Avatar
spooky1 spooky1 is offline
Member with 126 posts.
THREAD STARTER
 
Join Date: Jan 2010
27-Jun-2012, 01:41 AM #12
Thanks Keeballah, works like a charm.
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
27-Jun-2012, 11:06 AM #13
you're welcome!
spooky1's Avatar
spooky1 spooky1 is offline
Member with 126 posts.
THREAD STARTER
 
Join Date: Jan 2010
06-Jul-2012, 02:53 AM #14
Well, I managed, in your example.xls, to display more than one value using data in another sheet. But, when I tried to incorporate it into my program using right click `view code' I keep getting an ambiguos error message. I have lots of data in the change > Private Sub Worksheet_Change(ByVal Target As Range) of an existing sheet. I'm totally unfamiliar with the option explict commands.
I also want the code to work from data input via an inputbox or vba assigned value like x=104, lookup x rather than typing data in a cell on the sheet. I tried copying the option explicit code to a new module but nothing happens when I type something into a differently specified target cell. Am at a loss what to try next.

Last edited by spooky1; 06-Jul-2012 at 03:15 AM..
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,431 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
12-Jul-2012, 10:19 AM #15
I'm away on holiday now, will be back in the course of the following week.
Option explicitr is nothing more than to force variables to be dimensioned.
Soma bacsic VBA knowledge is advisable if you want to use code such as macro's to make them do what you want them to do.
I suggest you take a look at some sites that will give you the basics and help you on the way.
I'll take a look when I'm back and see if I can help you along.
BTW ambiguous etc. etc means that you a duplication code.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑