Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP without a sorted list
Hello,
I have a worksheet containing a list of parts with two different part numbers assigned to each (sorted by the first part number). I have another worksheet with a partial list of the second type of part number. I want to fill the column next to it with it's corresponding part number. Here are the problems with the functions embedded in Excel: 1. VLOOKUP can only lookup lists only if they're sorted in ascending order in the first column. 2. LOOKUP doesn't have the option to give 'exact' results. Here's a simplified example of my dilemma: --SHEET 1-- Alias 1 Alias 2 1 B 2 A 3 C 4 D 5 E --SHEET 2-- Alias 1 Alias 2 ??? D ??? E Please note that for this example, LOOKUP will work just fine. But in my actual case, Alias 1 and/or 2 part numbers can be missing. Do I need to resort to writing a macro for this? Or can I work with the tools Excel has provided for me to accomplish this? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP without a sorted list
I figured it out, I used the INDEX() and a nested MATCH() function:
=INDEX(Worksheet1,MATCH(Reference,[WorkSheet1 Alias2 Row],0),1) "dzuy" wrote: Hello, I have a worksheet containing a list of parts with two different part numbers assigned to each (sorted by the first part number). I have another worksheet with a partial list of the second type of part number. I want to fill the column next to it with it's corresponding part number. Here are the problems with the functions embedded in Excel: 1. VLOOKUP can only lookup lists only if they're sorted in ascending order in the first column. 2. LOOKUP doesn't have the option to give 'exact' results. Here's a simplified example of my dilemma: --SHEET 1-- Alias 1 Alias 2 1 B 2 A 3 C 4 D 5 E --SHEET 2-- Alias 1 Alias 2 ??? D ??? E Please note that for this example, LOOKUP will work just fine. But in my actual case, Alias 1 and/or 2 part numbers can be missing. Do I need to resort to writing a macro for this? Or can I work with the tools Excel has provided for me to accomplish this? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP without a sorted list
Vlookup can be set to only retrun exact matches and not require the list to
be sorted using the Optional 4th argument. The default of the 4th argument is true meaning that it looks for a closest match, but if you speicify false then it will only return exact matches. =Vlookup(A1, B1:C100, 2, False) Looks for an exact match to the value in A1 from the Column B1:C100 returning the second column value... -- HTH... Jim Thomlinson "dzuy" wrote: Hello, I have a worksheet containing a list of parts with two different part numbers assigned to each (sorted by the first part number). I have another worksheet with a partial list of the second type of part number. I want to fill the column next to it with it's corresponding part number. Here are the problems with the functions embedded in Excel: 1. VLOOKUP can only lookup lists only if they're sorted in ascending order in the first column. 2. LOOKUP doesn't have the option to give 'exact' results. Here's a simplified example of my dilemma: --SHEET 1-- Alias 1 Alias 2 1 B 2 A 3 C 4 D 5 E --SHEET 2-- Alias 1 Alias 2 ??? D ??? E Please note that for this example, LOOKUP will work just fine. But in my actual case, Alias 1 and/or 2 part numbers can be missing. Do I need to resort to writing a macro for this? Or can I work with the tools Excel has provided for me to accomplish this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: VLOOKUP returns #N/A for a sorted value that exists?? WHY? | Excel Worksheet Functions | |||
sorted one list based on another one | Excel Discussion (Misc queries) | |||
Why must the table for Vlookup be sorted in ascending order? | New Users to Excel | |||
Sorted list | Excel Discussion (Misc queries) | |||
Need sorted validation list | Excel Worksheet Functions |