How to get correct values without applying filters

Hi All,

I need your help to find the correct values by formulas.

I have data in four Columns
Col A - Country Name
Col B - Vehicle Type
Col C - Price 1
Col D - Price 2

I have to 1st identify the Country Name (col A), then find out the correct "Price 2" value (col D) of a particular Vehicle Type (col B)

Now I have to identify/arrange the data from these columns to a new layout, where I know how to identify the results from col D.
I just apply the 1st filter on a Country Name (col A),
2nd filter on Vehicle Type (col B),
3rd filter on Price 2 (col D)

The attached file has the both the layouts... Please help me!

Thanks in advance!
With Regards,
SunOffice

Hi there.

1 Way is using this ARRAY formula in I2 and copy down and across.

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

OR using SUMPRODUCT function.

Formula:

Please Login or Register  to view this content.

like this formula in I2 filled across then down
=INDEX($D$2:$D$120,MATCH($H2&I$1,INDEX($A$2:$A$120&$B$2:$B$120,0),0))
ps(your original had calculation set to manual)
[pps no arrays were abused in the construction of this function ]

Thanks you Fotis1991, .....Sumproduct is the best!

Thank you martindwilson, .... I have seen the formulas in the workbook. So do you mean I don't need to apply Arrays by Ctrl+Shift+Enter keys? ...Awesome!

I have also figure out another formula:

Formula:

Please Login or Register  to view this content.

...but i don't know if it will work on Excel 2003 application too.

btw... I have a query to both of you, Please let me know how can use the entire column instead of a range in your formulas, like in the AVERAGEIFS formula, I have taken D:D, A:A columns.

Averageifs does not works in Excel<2003.

To use whole column reference is not a good idea. As a rule the calculations of your sheet will be much more slow...Special when we use ARRAY formulas.

Alright Fotis1991!! I will take the ranges, not the entire columns.

Btw... Since the AVERAGEIFS formula also gives us the results... but is it the accurate as per my need (plz see the 1st post above)?
and which is the fastest formula/technique?

Your one(with a small modification!should be the faster.

In I2 and copy down and across.

Formula:

Please Login or Register  to view this content.

well you might as well use sumifs then
=SUMIFS($D:$D,$A:$A,$H2,$B:$B,I$1)

Thanks a ton to you both genius!