1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: multi value vlookup

Discussion in 'Business Applications' started by spooky1, Jun 17, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    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:

  2. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    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:

  3. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    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?
     
  4. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    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.
     
  5. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    I have tried something using conditional format but do not know if it works with Excel 2003
    You might get compatability issues
     

    Attached Files:

  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    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.
     
  7. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    Yeah, but the porster wants to see all the occurrences of the cell containing a name or part of a name. (see #3)
     
  8. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    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
     
  9. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    Not with a formula but you can di this with a macro
     
  10. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    Hi Keballa,
    doing it with a macro is fine, where do I find such a macro?
     
  11. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    You write it!!!
     

    Attached Files:

  12. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    Thanks Keeballah, works like a charm.
     
  13. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    :) you're welcome!
     
  14. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    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.
     
  15. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    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.
     
  16. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1057433