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)) |
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)) |
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)) |
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)) |
All times are GMT +1. The time now is 03:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com