VLOOKUP/MATCH Query - quite complex....but interesting.
sure,
i fyou look at table 2 you'll see that it is the same size as table 1. the r
and n are just the letters in the equivalent position in tabel 2.
e.g. 5.69 is the highest value in table 1; shop 3, company: dixons.
in table 2, shop 3 and ixons give letter r....so i would want to report
letter r.
"Mike H" wrote:
Sam,
I'm being a bit slow on the uptake here but why does the r appear, what is
r?, what are the rules?
Mike
dixons shop3 5.69 r
"SAM" wrote:
made a mistake in the bit about my new column...SORRY.....Updated below:
"l" and "h" should actually be:
company shop value table 2 value
dixons shop3 5.69 r
c&a shop4 2.5 n
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95
"SAM" wrote:
I have the following data sets:
table 1 - a number comparison values for different companies and shops
shop1 shop2 shop3 shop4 shop5
0.00015 0.23 0.09 0.1 0.69 vod
0.5 0.00005 0.999 0.26 0.95 tesco
0.93 1.56 0.94 2.5 0.32 c&a
0.0003 0.73 5.69 0.47 0.00001 dixons
I summarise my data below by taking the top 5 largest values and the
correponding companies and shops:
summary table
company shop value
dixons shop3 5.69
c&a shop4 2.5
c&a shop2 1.56
tesco shop3 0.999
tesco shop5 0.95
i now want to add another column to my summary table above by sourcing data
from table 2 at the bottom of the page.
THIS COLUMN
company shop value table 2 value
dixons shop3 5.69 l
c&a shop4 2.5 h
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95
Thing is - the summary table will change all the time as i rank the top
5....the data in real life is live...and so i need a formula in the 'table 2
value column' as opposed to just a straight v look up that would look up the
same position everytime....any thoughts?
Thanks to all the seriously bright people who've helped me on this as well.
table 2
shop1 shop2 shop3 shop4 shop5
a b c d e vod
f g h i j tesco
k l m n o c&a
p q r s t dixons
ps. some formula's i've used so far (for other people's benefit)
1. to look up and rank the top 5 biggest companies
=INDEX($G$4:$G$7,MAX(($B$4:$F$7=LARGE($B$4:$F$7,RO W(B1:G1)))*ROW($B$4:$F$7)-MIN(ROW($B$4:$F$7))+1))
2. to look up and rank the top 5 biggest corresponding shops
=INDEX($B$3:$F$3,MAX(($B$3:$F$7=LARGE($B$3:$F$7,RO W(B1)))*COLUMN
($B$3:$F$7)-MIN(COLUMN($B$3:$F$7))+1))
3. to look up and rank the corresponding top 5 biggest values that go with
the above information
=LARGE(B$4:F$7,ROWS($3:3))
|