Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup formula for retuning multiple values
Hi all, i need help. I am working with two different list of assets, i
am trying to populate one of the lists with data from the other list. i have tried using vlookup, but the problem i have is that i am using the description of the assets as the search criteria, but i have situations where i have the same description for more than one asset, so when using the vlookup function, it returns the same value for the first of the occurnece for all the values with the same description, is there anything else i can do. Thanks for your anticipated help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup formula for retuning multiple values
On Jul 14, 3:15*am, Ayobami Adeloye wrote:
Hi all, i need help. I am working with two different list of assets, i am trying to populate one of the lists with data from the other list. i have tried using vlookup, but the problem i have is that i am using the description of the assets as the search criteria, but i have situations where i have the same description for more than one asset, so when using the vlookup function, it returns the same value for the first of the occurnece for all the values with the same description, is there anything else i can do. Thanks for your anticipated help. Post samples or "If desired, send your file to dguillett @gmail.com I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results." |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup formula for retuning multiple values
You need to look at the SMALL function. If you use this properly (it's
explained ok in the help files), you can use it to show all the assets with the same name. The line below is from a spreadsheet that I use to track inductions. when I look for a name, there are often many people with the same surname. The formula below finds the first instance - ROW(1,1) - then the next row - ROW(2,2) etc. You will have to tailor the formula to what you need, and copy it down enough cells so that you catch all instances. OM =IF(ISERROR(INDEX(AllData,SMALL(IF(Surname=SName,R OW(Surname)-1),ROW(2:2)),2)),"",INDEX(AllData,SMALL(IF(Surname =SName,ROW(Surname)-1),ROW(2:2)),2)) On Wed, 14 Jul 2010 01:15:28 -0700 (PDT), Ayobami Adeloye wrote: Hi all, i need help. I am working with two different list of assets, i am trying to populate one of the lists with data from the other list. i have tried using vlookup, but the problem i have is that i am using the description of the assets as the search criteria, but i have situations where i have the same description for more than one asset, so when using the vlookup function, it returns the same value for the first of the occurnece for all the values with the same description, is there anything else i can do. Thanks for your anticipated help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula Using Multiple Values | New Users to Excel | |||
Multiple lookup values and adding multiple rates across together | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Retuning a column number for a specific value | Excel Worksheet Functions | |||
Lookup multiple values on multiple sheets | Excel Programming |