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!

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!

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!