Formula for two pieces of information

jo.stanley

Board Regular
Joined
Apr 13, 2004
Messages
177
I hope I can explain this so it makes sense!

I have a spreadsheet that on one sheet has a matrix

e.g. (a) 2 months free + (b) 3 = (c) 15122
(a) Best of Deal + (b) 4 = (c) 17822

To explain the information above the (a) is text which describes a deal (b) is just a number that can be from 1 to 11 for any deal (c) is a unique figure that is based on the criteria of (a) and (b).

I also have a second sheet that only shows (a) and (b) and I need to write a formula that takes the information from the above matrix and adds (c) to this second sheet.

I have no idea where to start and I wonder if someone can assist in any way. :oops:
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jo.stanley

Board Regular
Joined
Apr 13, 2004
Messages
177

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632

ADVERTISEMENT

If I understand you correctly you could do this:

In cell D1 of sheet1 where your 3 columns of data are =A1&B1
and fill down.
Then in C1 of sheet2 where you want to add the corresponding data from sheet1 =INDEX(Sheet1!$C$1:$C$10,MATCH(A1&B1,Sheet1!$D$1:$D$10,0))
and fill down.
I don't know your full ranges so I just went down 10 rows so you'll have to adjust for your ranges of course.
 

jo.stanley

Board Regular
Joined
Apr 13, 2004
Messages
177
This is the first spreadsheet that contains the matrix
Book1
ABCDEF
112345
2CostofFuel15313062459361247655
3LivingExpenses918610717122491378015311
4Health17073403510468058506
Sheet1


And this is the second where i want to import the data from the matrix to
Book1
ABCD
10CostofFuel2
11LivingExpenses4
12CostofFuel1
13Health3
14Health2
Sheet1


So the result I need is

Cost of Fuel 2 = 3062

I hope this makes more sense unfortunately i cannot use the actual database as information is confidential
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
ok....same function different matchs.....

=INDEX(Sheet1!$B$2:$F$4,MATCH(A1,Sheet1!$A$2:$A$4,0),MATCH(B1,Sheet1!$B$1:$F$1,0))
 

Forum statistics

Threads
1,147,621
Messages
5,742,193
Members
423,710
Latest member
Duarte85

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
Top