• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to return a text cell sorting values between 2 different tables

A

aleksi

New Member
Hello everybody,


I'm trying to return a text cell from 2 tables. These 2 tables contain numerical values resulting of a mathematical formula, so inside the cell there is the number as result.


As shown below I'd like to return for instance "SPAGHETTI, 500 G" in a new cell if the value "131.6455696" is the highest in the other table. I've tried with many functions but with no luck.


How could I fix it?


Product / Year 1 RICE,1 KG CRISPBREAD, 1 KG SPAGHETTI, 500 G


2002 2.45 4.39 0.79

2003 2.46 4.49 0.78

2004 2.42 4.53 0.76

2005 2.18 4.62 0.75

2006 1.98 4.26 0.79

2007 2 4.28 0.8

2008 2.12 4.48 1.04


Price relative Index


2002 100 100 100

2003 100.4081633 102.2779043 98.73417722

2004 98.7755102 103.1890661 96.20253165

2005 88.97959184 105.23918 94.93670886

2006 80.81632653 97.03872437 100

2007 81.63265306 97.49430524 101.2658228

2008 86.53061224 102.0501139 131.6455696


Thanks in advance,


Aleksi
 
Aleksi


Assuming that Product is in Cell A1 and that you are after the largest value in the latest Row ie: 2008 the following will work


=OFFSET($A$1,0,MATCH(MAX(OFFSET($B$1:$D$1,COUNTA($A$2:$A$22),0)),OFFSET($B$1:$D$1,COUNTA($A$2:$A$22),0)))


If you are after the product with the maximum value anywhere in the table

Add A helper Column Column E

In E1 put =MAX(B2:D8)

In E2 put =IF(ISERROR(MATCH($E$1,B2:D2,0)),0,MATCH($E$1,B2:D2,0))

copy E2 down


Now you can use

=+OFFSET($A$1,0,MAX($E$2:$E$8))

to return the maximum value from anywhere
 
Aleksi

Apologies, I've misread your tables

Can you please tell us the cells which

Product / Year 1 RICE,1 KG CRISPBREAD, 1 KG SPAGHETTI, 500 G

and

2002 100 100 100

are in ?
 
Hey Hui,

I've tried what you suggested and we are almost there.

But still it doesn't work. The table i posted there is not the real one. The real one is much bigger with many items.


I try to post it here:


TABLE 1:


Product / Year 1 RICE,1 KG CRISPBREAD, 1 KG SPAGHETTI, 500 G

2002 2.45 4.39 0.79

2003 2.46 4.49 0.78

2004 2.42 4.53 0.76

2005 2.18 4.62 0.75

2006 1.98 4.26 0.79

2007 2 4.28 0.8

2008 2.12 4.48 1.04


TABLE 2


Price relative Index


2002 100 100 100

2003 100.4081633 102.2779043 98.73417722

2004 98.7755102 103.1890661 96.20253165

2005 88.97959184 105.23918 94.93670886

2006 80.81632653 97.03872437 100

2007 81.63265306 97.49430524 101.2658228

2008 86.53061224 102.0501139 131.6455696


Thanks again for the prompt reply before :)


Br,


Aleksi
 
Aleksi

Can you please tell me the Cell Reference of the two Year 2002 cells


ie:

Product/Yr 2002 is in Sheet1 A3

Price Relative Index 2002 is in Sheet3 A20

etc
 
You must log in or register to reply here.
Back
Top