Lookup

[Roger]

The following is the data:

Part# Price1 Price2 Price3 Rank1 Rank2 Rank3
a1 10 25 10 1 3 1
a2 60 60 45 2 2 1

I want to recast the above data in the order of the rank as follows:

Part# Price1 Price2 Price3 Rank1 Rank2 Rank3
a1 10 10 25 1 1 3
a2 45 60 60 1 2 2

Basically, it boils down as to how to lookup multiple values for the same
value. Vlookup accesses the same value for the first occurence only.

I would greatly appreciate your suggestion. Thanks in advance.

Roger

 

[Dave Peterson]

I don't quite see how =vlookup() fits here, but couldn't you just sort the rows
(columns B).

If you select B22 and do data|sort, you'll see an Options Button on that
dialog.

If you click that button, you'll see where you can sort by row.

If you have lots of rows to sort, you may want to use a macro:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
With Worksheets("sheet1")
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
For Each myCell In myRng.Cells
With myCell.Resize(1, 3)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo, _
Orientation:=xlSortRows
End With
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm