How do I create a formula using multiple variables?

alsexceladmin

Board Regular
Joined
Jan 17, 2005
Messages
62
I have a spreadsheet set up along with another two ref sheets (Range 1 and 2) and a result sheet. (all on the same page)
Apologies - (I cannot unfortunately download the software at this location to allow me to submit the sheet ) But if it should become necessary, I will do so from home later!
(Range 1) consists of 5 rows and seven columns, each of these rows/cols begin with a text cell, all the other cells contain a number value.
(Range 2 is similar)

I am trying to create a formula to do the following:
I need to identify in turn, the contents of two seperate cells (both text) from the same row in the spreadsheet, then using the two - reference/check lookup (whatevers req) with Range (1) to produce the relevant number value given within this range where the row/cols intersect, then place the result in another cell in the result sheet.
I have got an example set up but for reasons given, cannot yet post the sheet. Sorry!

I am running office 2003 with XP Pro.

Hope you can help.


alsexceladmin :rolleyes:
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Ron Morris

Active Member
Joined
Oct 18, 2004
Messages
430
Hi,

This is kinda rough without having more detail but I hope you get the idea.

You have indicated that your one sheet is 5 rows, and 7 columns, let's say A1:G5.

I am assuming you have another area, though it sounds like it may be the same area, that you need to find where they match. Just so I can provide an example, let's say that it's range is J1:P5

Assuming that you will be looking up the value in column A of range A1:G5 and for values in columns C (or 3) and E (or 5).

=INDEX($J$1:$P$5,MATCH(VLOOKUP(A10,$A:$G,3,FALSE),$J$1:$J$5,),MATCH(VLOOKUP(A10,$A:$G,5,FALSE),$J$1:$P$1)

Again, tough to do without the lookup data but I hope you get the idea.

This also assumes you will be comparing data from your original range and that these values will be column headings when you are attempting to match them. The Indexed range should be sorted in ascending order.

Also, if there is no match with either the Vlookup or Index/Match, #N/A will be the result.

Ron
 

alsexceladmin

Board Regular
Joined
Jan 17, 2005
Messages
62
Thanks for your response.
I am still trying various combos without, as yet, any success.
When I finally get HtmlMaker to work at home (probs in another posting), I hope to post a sample of the sheet, to enable all you kind souls to help me with a solution to my formula creation problem.

Can anyone tell me why my post header has now turned RED and how I can turn it to normal like everyone else?


Many thanks Ron & all - alsexceladmin
 

Forum statistics

Threads
1,148,055
Messages
5,744,544
Members
423,882
Latest member
Seeham

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