How to perform two way look-up across different sheets?

X

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
531
Office Version
  1. 2010
Platform
  1. Windows
Dear All,
I would like to perform a 2 way lookup across 2 different sheets.
In the Sheet1 I do have the following Store-wise Weight-Slab-wise rate matrix spread across A2:E6(from where the look-up has to happen).
Sheet1

*ABCDE
*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 41px;"><col style="width: 46px;"><col style="width: 46px;"><col style="width: 46px;"><col style="width: 58px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="colspan: 4, align: center"]Weight Matrix Kg(As Per Slabs)[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]Store[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]200[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]$1,000[/TD]
[TD="align: center"]$1,200[/TD]
[TD="align: center"]$1,400[/TD]
[TD="align: center"]$1,600[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]$1,500[/TD]
[TD="align: center"]$1,800[/TD]
[TD="align: center"]$2,100[/TD]
[TD="align: center"]$2,400[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]$2,000[/TD]
[TD="align: center"]$2,400[/TD]
[TD="align: center"]$2,800[/TD]
[TD="align: center"]$3,200[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]$2,500[/TD]
[TD="align: center"]$3,000[/TD]
[TD="align: center"]$3,500[/TD]
[TD="align: center"]$4,000[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
The store names are across the column A(A3:A6) and the rates across these 4 stores under different slabs are present across B3:E6.
For example,The applicable rate for 50 Kg for store A is $1000 and the same for 100kg for storeA is $1200 and so on.
Now based on this matrix in Sheet1 I want to calculate the freight rate based on store & weight-slab in Sheet2:-

Sheet2

*ABC

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 38px;"><col style="width: 72px;"><col style="width: 129px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Store[/TD]
[TD="align: center"]Weight(Kg)[/TD]
[TD="align: center"]Rate(Desired Result)[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]50[/TD]
[TD="bgcolor: #FFFF00, align: center"]$1,000[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]150[/TD]
[TD="bgcolor: #FFFF00, align: center"]$1,400[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]200[/TD]
[TD="bgcolor: #FFFF00, align: center"]$2,400[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]150[/TD]
[TD="bgcolor: #FFFF00, align: center"]$2,800[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]200[/TD]
[TD="bgcolor: #FFFF00, align: center"]$4,000[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
To further explain, I want the formula across C2:C6(in Sheet2) to match the store and the weight (from Sheet1) and subsequently pick up the Rate from the data set spread across A2:E6(from Sheet1).
Would be grateful if somebody helps with the correct formula.
Regards
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
xlmaniac said:
Dear All,
I would like to perform a 2 way lookup across 2 different sheets.
In the Sheet1 I do have the following Store-wise Weight-Slab-wise rate matrix spread across A2:E6(from where the look-up has to happen).
Sheet1

*
A
B
C
D
E
*
Store
50
100
150
200
A
$1,000
$1,200
$1,400
$1,600
B
$1,500
$1,800
$2,100
$2,400
C
$2,000
$2,400
$2,800
$3,200
D
$2,500
$3,000
$3,500
$4,000

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="colspan: 4"]Weight Matrix Kg(As Per Slabs)
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
The store names are across the column A(A3:A6) and the rates across these 4 stores under different slabs are present across B3:E6.
For example,The applicable rate for 50 Kg for store A is $1000 and the same for 100kg for storeA is $1200 and so on.
Now based on this matrix in Sheet1 I want to calculate the freight rate based on store & weight-slab in Sheet2:-

Sheet2

*
A
B
C
Store
Weight(Kg)
Rate(Desired Result)
A
50
A
150
B
200
C
150
D
200

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #ffff00"]$1,000
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="bgcolor: #ffff00"]$1,400
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="bgcolor: #ffff00"]$2,400
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="bgcolor: #ffff00"]$2,800
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

[TD="bgcolor: #ffff00"]$4,000
[/TD]

</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
To further explain, I want the formula across C2:C6(in Sheet2) to match the store and the weight (from Sheet1) and subsequently pick up the Rate from the data set spread across A2:E6(from Sheet1).
Would be grateful if somebody helps with the correct formula.
Regards
Click to expand...


C2 down:
=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,0))
 
Upvote 0
Robert Mika said:
C2 down:
=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,0))
Click to expand...

Dear Mr Mika,
Thank you so much for helping me yet again with your mesmerising solution.
Really appreciate the same.
I feel honoured to learn excel from the gurus like you.
One last question, incase the weight of the shipment falls between 2 slabs and I would like to match basis on the next higher slab then what will be the formula?
Thanks once again for all your support
 
Upvote 0
Change the second row in Sheet1 to read
0,50,100,150 and then in Sheet2

=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,1))
 
Upvote 0
Robert Mika said:
Change the second row in Sheet1 to read
0,50,100,150 and then in Sheet2

=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,1))
Click to expand...

Dear Mr Mika,
You have created a WOW for me!!!:):)
Please accept my sincere gratitude for the same....
Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A
K
  • Question Question
Autofill across based on previous row
Replies
0
Views
535
krbreddy
K
P
  • Solved
The calculation in Excel works, but only in a single row
2
Replies
15
Views
2K
Fluff
Fluff
Sean15
  • Question Question
How to identify duplicates using two criteria
Replies
3
Views
715
Fluff
Fluff
Q
  • Question Question
2 D look up Index Match across Multiple Sheets i.e. If not in S1, then S2, If not S2 then S3.
Replies
1
Views
485
gaz_chops
G

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top