Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 29th 09, 01:43 PM posted to microsoft.public.excel.misc
Sam Sam is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 699
Default VLOOKUP/MATCH Query - quite complex....but interesting.

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

  #2   Report Post  
Old June 29th 09, 01:46 PM posted to microsoft.public.excel.misc
Sam Sam is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 699
Default VLOOKUP/MATCH Query - quite complex....but interesting.

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

  #3   Report Post  
Old June 29th 09, 03:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default VLOOKUP/MATCH Query - quite complex....but interesting.

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

  #4   Report Post  
Old June 29th 09, 03:40 PM posted to microsoft.public.excel.misc
Sam Sam is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 699
Default 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))



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help! with Parameter Query for complex SQL robottv Excel Discussion (Misc queries) 0 April 10th 08 01:58 PM
Match with Complex Lookup_array karlsven Excel Worksheet Functions 2 December 20th 07 09:18 AM
IF function query (complex) Hammond-Wandsworth Excel Worksheet Functions 3 August 31st 07 03:42 PM
Complex Index Match Help (or at least complex to me) Jennifer Reitman Excel Discussion (Misc queries) 3 August 10th 06 08:51 PM
Complex query question Stacy Excel Worksheet Functions 0 June 30th 05 09:50 PM


All times are GMT +1. The time now is 05:15 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017