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

S

#### 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))

S

#### SAM

"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

M

#### 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

S

#### 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.