Help with a formula: result according to two categories of conditions

C

costud

New Member
Joined
Aug 10, 2011
Messages
4
Hello!
I have two columns. The first contain 8 types of features (let's say caracta1 ... caracta8) and the second three types of characteristics of a product (let's say caractb1 ... caractb3).
I need a formula that returns a result (written in another cells), depending on the combination of the characteristics of the two columns.
I mean, if the product contains features caracta1 and caractb1, return the result of cell x1, if contains the result of caracta1 and caractb2, return the resunt contained in x2, etc ...
Winth IF(AND(..);IF(AND(... didn't work, because the formula is too big for Excel 2003 :cry:
Thank you! :beerchug:
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Welcome to the Board!

If it is a single value you're wanting returned, try:

Code: 
=INDEX(DataToBeReturnedRange,MATCH(1,IF(TypesRange="caracta1",IF(ProductRange="caractb1",1)),0))
This is an array formula and therefore needs committing with CTRL+SHIFT+ENTER.

Hope this helps.

Matty
 
Upvote 0
Thank you Matty for your quick response. I cannot be more explicit but here you can find the formula I tried and which is too big:

=IF(AND(A3="caracta1";G3="caractb1");O15;IF(AND(A3="caracta1";G3="caractb2");P15;IF(AND(A3="caracta1";G3="caractb3");Q15;IF(AND(A3="caracta2";G3="caractb1";O16;IF(AND(A3="caracta2";G3="caractb2");P16;IF(AND(A3="caracta2";G3="caractb3");Q16;IF(AND(A3="caracta3";G3="caractb1");O17;IF(AND(A3="caracta3";G3="caractb2");P17;IF(AND(A3="caracta3";G3="caractb3");Q17;IF(AND(A3="caracta4";G3="caractb1");O18;IF(AND(A3="caracta4";G3="caractb2");P18;IF(AND(A3="caracta4";G3="caractb3");Q18;IF(AND(A3="caracta5";G3="caractb1");O19;IF(AND(A3="caracta5";G3="caractb2");P19;IF(AND(A3="caracta5";G3="caractb3");Q19;IF(AND(A3="caracta6";G3="caractb1");O20;IF(AND(A3="caracta6";G3="caractb2");P20;IF(AND(A3="caracta6";G3="caractb3");Q20;IF(AND(A3="caracta7";G3="caractb1");O21;IF(AND(A3="caracta7";G3="caractb2");P21;IF(AND(A3="caracta7";G3="caractb3");Q21;IF(AND(A3="caracta8";G3="caractb1");O22;IF(AND(A3="caracta8";G3="caractb2");P22;IF(AND(A3="caracta8";G3="caractb3");Q22;"ERROR")))))))))))))))))))))))))
 
Upvote 0
I can see what you're trying to do, but an example will help make it clearer.

To post some sample data, ensure there are borders around the Cells, select them, hit copy, come back to your message and paste the contents.

Matty
 
Upvote 0
Try...
Code: 
=IF(AND(RIGHT(A3)+0>=1,RIGHT(A3)+0<=8),
   IF(AND(RIGHT(G3)+0>=1,RIGHT(G3)+0<=3),
    INDEX($O$15:$Q$22,RIGHT(A3)+0,RIGHT(G3)+0),""),"")
 
Upvote 0
Ok, here is the example:

ODZ Nivel_tensiune Pret_Actual
Banat MT
Banat JT
Banat JT
Banat JT
Banat MT
Banat MT
Banat JT
Banat MT
Banat MT
Banat MT
Banat JT
Trans_Nord JT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Trans_Nord MT
Trans_Nord JT
Trans_Nord JT
Trans_Nord MT
Trans_Nord MT
Trans_Nord MT
Trans_Nord JT
Trans_Nord JT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Oltenia MT
Oltenia JT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia JT
Oltenia JT
Oltenia JT
Oltenia JT
Oltenia MT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea MT
Dobrogea MT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea MT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud JT
Munt_Sud JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord JT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord MT
Munt_Nord MT
Munt_Nord MT
Munt_Nord JT
Moldova MT
Moldova JT
Moldova JT
Moldova MT
Moldova JT
Moldova MT
Moldova JT
Moldova MT
Moldova MT
Moldova MT
Moldova JT
Moldova JT
Moldova JT
Moldova JT

On other sheet, I have:
ODZ IT MT JT
Trans_Nord 23 100 190
Banat 24 103 189
Oltenia 35 106 156
Munt_Sud 12 99 176
Dobrogea 45 108 194
Moldova 33 112 177
Munt_Nord 32 115 193

In column Pret actual, I want to put the result of intersection of the table from other sheet, according to "ODZ" and "Nivel tensiune" matches.
Thank you again.
 
Upvote 0
costud said:
Ok, here is the example:

ODZ Nivel_tensiune Pret_Actual
Banat MT
Banat JT
Banat JT
Banat JT
Banat MT
Banat MT
Banat JT
Banat MT
Banat MT
Banat MT
Banat JT
Trans_Nord JT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Trans_Nord MT
Trans_Nord JT
Trans_Nord JT
Trans_Nord MT
Trans_Nord MT
Trans_Nord MT
Trans_Nord JT
Trans_Nord JT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Trans_Nord JT
Trans_Nord MT
Oltenia MT
Oltenia JT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia MT
Oltenia JT
Oltenia JT
Oltenia JT
Oltenia JT
Oltenia JT
Oltenia MT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea MT
Dobrogea MT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea JT
Dobrogea JT
Dobrogea JT
Dobrogea MT
Dobrogea MT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud JT
Munt_Sud MT
Munt_Sud JT
Munt_Sud JT
Munt_Sud JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord JT
Munt_Nord JT
Munt_Nord MT
Munt_Nord JT
Munt_Nord MT
Munt_Nord MT
Munt_Nord MT
Munt_Nord JT
Moldova MT
Moldova JT
Moldova JT
Moldova MT
Moldova JT
Moldova MT
Moldova JT
Moldova MT
Moldova MT
Moldova MT
Moldova JT
Moldova JT
Moldova JT
Moldova JT

On other sheet, I have:
ODZ IT MT JT
Trans_Nord 23 100 190
Banat 24 103 189
Oltenia 35 106 156
Munt_Sud 12 99 176
Dobrogea 45 108 194
Moldova 33 112 177
Munt_Nord 32 115 193

In column Pret actual, I want to put the result of intersection of the table from other sheet, according to "ODZ" and "Nivel tensiune" matches.
Thank you again.
Click to expand...
Sheet1 houses the long exhibit in A2:B100, excluding the headers, Sheet2 the table with numbers in A1:D8, including the headers.

On Sheet1 in C2 enter and copy down:

=INDEX(Sheet2!$B$2:$D$8,MATCH($A2,Sheet2!$A$2:$A$8,0),MATCH($B2,Sheet2!$B$1:$D$1,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Is it possible to insert/delete rows in a file and make other files automatically update their rows accordingly?
Replies
3
Views
412
jkpieterse
jkpieterse
Jyggalag
  • Solved
Conditional formatting for blank cells containing formulas?
Replies
8
Views
5K
AgingHippie
A
Jyggalag
  • Question Question
Text to columns issues
Replies
2
Views
725
Jyggalag
Jyggalag
L
  • Question Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
462
Lukma
L
R
  • Question Question
IFERROR in Data Validation Formula
Replies
2
Views
5K
Rafaa
R

Forum statistics

Threads
1,224,583
Messages
6,179,672
Members
452,937
Latest member
Bhg1984

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