VLOOKUP/MATCH Query - quite complex....but interesting.

[SAM]

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,ROW(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,ROW(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))

 

[SAM]

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

 

[Mike H]

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

 

[SAM]

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.