Sep 26 2019 10:18 AM
I have a matrix of state to state rates in the contract-state...tab.
The origin state is in ColumnA and the and the Destination state is in Row1
On Sheet1 I have the ST-ST set column A. I would like to have the rates populate in column B. to make vlookup easier.
Sep 26 2019 02:29 PM
@ESKARDA Are you sure you attached the correct file? It's a CSV file and there is no Sheet1, just two columns of data.
What exactly is your question? Do you want to know how to do a Vlookup?
Please help us understand what you want to achieve.
Sep 27 2019 06:54 AM
My apologies. I attached the correct spreadsheet.
In the Contract-State-R tab is a state to state rate matrix. The Origin states are in Column A and the destination states are on row 2. I am looking for the most efficient way to get the rates into Column B (next to the corresponding ST-ST in Column A)
Sep 27 2019 02:13 PM
If I understood your logic correctly
For such source matrix
convert it to two columns (J and K) could be used
in J3
=INDEX($B:$B, INT( (ROW()-ROW($B$2)-1)/$J$1)+ROW($B$2)+1) &
"-" &
INDEX($C$2:$H$2,MOD(ROW()-ROW($B$2)-1,$J$1)+1)
in K3
=INDEX(
INDEX(C:H,
INT( (ROW()-ROW($B$2)-1)/$J$1)+
ROW($B$2)+1,0
),
MOD(ROW()-ROW($B$2)-1,$J$1)+1
)
and need to be adjusted to your actual ranges and sheets.
Oct 02 2019 07:02 AM
You are a life saver thank you so much@Sergei Baklan
Oct 02 2019 07:10 AM
@ESKARDA , you are welcome