|
VLOOKUP in a RANGE.
Would really appreciate any ideas...
I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
Hi,
Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
it doesn't seem to have worked....can i send you something - it might make
more sense...i still get a ref# error. "Mike H" wrote: Hi, Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
Sam,
I prefer to keep postings in the group. You can post some sample data here or upload your file to the link below (It's free) and I'll take a look. http://www.savefile.com/ Mike "SAM" wrote: it doesn't seem to have worked....can i send you something - it might make more sense...i still get a ref# error. "Mike H" wrote: Hi, Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
Okay
well here is a sample range A B C D E F 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons I need a formula that scans the percentage data for the max value and then reports the corresponding company in colum f. The percentage change all the time so the company might change.. thank you so much. "Mike H" wrote: Sam, I prefer to keep postings in the group. You can post some sample data here or upload your file to the link below (It's free) and I'll take a look. http://www.savefile.com/ Mike "SAM" wrote: it doesn't seem to have worked....can i send you something - it might make more sense...i still get a ref# error. "Mike H" wrote: Hi, Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
Sam,
The formula I gave you work prefectly on this data and here are the 2 formula modified for the ranges in your sample data. Note that Tesco is both the Min and the Max so both formula return Tesco but if you enter another max then the formula update. VERY IMPORTANT, read the array formula instructions below. MIN =INDEX(F1:F4,MIN(IF(A1:E4=MIN(A1:E4),ROW(A1:E4)-MIN(ROW(A1:E4))+1))) Nax =INDEX(F1:F4,MAX((A1:E4=MAX(A1:E4))*ROW(A1:E4)-MIN(ROW(A1:E4))+1)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Okay well here is a sample range A B C D E F 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons I need a formula that scans the percentage data for the max value and then reports the corresponding company in colum f. The percentage change all the time so the company might change.. thank you so much. "Mike H" wrote: Sam, I prefer to keep postings in the group. You can post some sample data here or upload your file to the link below (It's free) and I'll take a look. http://www.savefile.com/ Mike "SAM" wrote: it doesn't seem to have worked....can i send you something - it might make more sense...i still get a ref# error. "Mike H" wrote: Hi, Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
Okay - WOOHOOO it works.....guy's i have literally been trying to do that for
days.... next question: if i also had different labels across the top: A B C D E F shop0 store1 store2 shop3 shop 4 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons so far i can find the min and max for the column f...but would it be posible to also find the column header/label with a sepearte formula basically so that i can say that for the MIMIMUM in the range: it's e.g tesco and shop 1. 2. Instead of the max/min - can this be done for the top five/bottom five.....for this do i just do max-1? "Don Guillett" wrote: I just tested Mikes formula, modified to your sample and got the correct result. =INDEX(F1:F20,MAX((A1:E20=MAX(A1:E20))*ROW(A1:E20)-MIN(ROW(A1:E20))+1)) Did you remember to ARRAY enter -- Don Guillett Microsoft MVP Excel SalesAid Software "SAM" wrote in message ... Okay well here is a sample range A B C D E F 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons I need a formula that scans the percentage data for the max value and then reports the corresponding company in colum f. The percentage change all the time so the company might change.. thank you so much. "Mike H" wrote: Sam, I prefer to keep postings in the group. You can post some sample data here or upload your file to the link below (It's free) and I'll take a look. http://www.savefile.com/ Mike "SAM" wrote: it doesn't seem to have worked....can i send you something - it might make more sense...i still get a ref# error. "Mike H" wrote: Hi, Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
A bit of thinking would have given you the answer.
=INDEX(1:1,MAX((A1:E20=MAX(A1:E20))*COLUMN(A1:E20)-MIN(COLUMN(A1:E20))+1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "SAM" wrote in message ... Okay - WOOHOOO it works.....guy's i have literally been trying to do that for days.... next question: if i also had different labels across the top: A B C D E F shop0 store1 store2 shop3 shop 4 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons so far i can find the min and max for the column f...but would it be posible to also find the column header/label with a sepearte formula basically so that i can say that for the MIMIMUM in the range: it's e.g tesco and shop 1. 2. Instead of the max/min - can this be done for the top five/bottom five.....for this do i just do max-1? "Don Guillett" wrote: I just tested Mikes formula, modified to your sample and got the correct result. =INDEX(F1:F20,MAX((A1:E20=MAX(A1:E20))*ROW(A1:E20)-MIN(ROW(A1:E20))+1)) Did you remember to ARRAY enter -- Don Guillett Microsoft MVP Excel SalesAid Software "SAM" wrote in message ... Okay well here is a sample range A B C D E F 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons I need a formula that scans the percentage data for the max value and then reports the corresponding company in colum f. The percentage change all the time so the company might change.. thank you so much. "Mike H" wrote: Sam, I prefer to keep postings in the group. You can post some sample data here or upload your file to the link below (It's free) and I'll take a look. http://www.savefile.com/ Mike "SAM" wrote: it doesn't seem to have worked....can i send you something - it might make more sense...i still get a ref# error. "Mike H" wrote: Hi, Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
VLOOKUP in a RANGE.
sorry i don't quite get that formula - is that to run over my sample data
below? "Don Guillett" wrote: A bit of thinking would have given you the answer. =INDEX(1:1,MAX((A1:E20=MAX(A1:E20))*COLUMN(A1:E20)-MIN(COLUMN(A1:E20))+1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "SAM" wrote in message ... Okay - WOOHOOO it works.....guy's i have literally been trying to do that for days.... next question: if i also had different labels across the top: A B C D E F shop0 store1 store2 shop3 shop 4 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons so far i can find the min and max for the column f...but would it be posible to also find the column header/label with a sepearte formula basically so that i can say that for the MIMIMUM in the range: it's e.g tesco and shop 1. 2. Instead of the max/min - can this be done for the top five/bottom five.....for this do i just do max-1? "Don Guillett" wrote: I just tested Mikes formula, modified to your sample and got the correct result. =INDEX(F1:F20,MAX((A1:E20=MAX(A1:E20))*ROW(A1:E20)-MIN(ROW(A1:E20))+1)) Did you remember to ARRAY enter -- Don Guillett Microsoft MVP Excel SalesAid Software "SAM" wrote in message ... Okay well here is a sample range A B C D E F 1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone 2 0.5% 0.85% 0.05% 0.26% 0.95% tesco 3 0.93% 0.86% 0.94% 0.46% 0.32% c&A 4 0.19% 0.73% 0.56% 0.47% 0.46% dixons I need a formula that scans the percentage data for the max value and then reports the corresponding company in colum f. The percentage change all the time so the company might change.. thank you so much. "Mike H" wrote: Sam, I prefer to keep postings in the group. You can post some sample data here or upload your file to the link below (It's free) and I'll take a look. http://www.savefile.com/ Mike "SAM" wrote: it doesn't seem to have worked....can i send you something - it might make more sense...i still get a ref# error. "Mike H" wrote: Hi, Try these 2 array formula Max =INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1)) Min =INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20 )-MIN(ROW(A1:M20))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "SAM" wrote: Would really appreciate any ideas... I have a range of data A1:M20 containing a number of different percentages. I want to look up the max and min and get it to report the corresponding name in column N (the column to the right of the range). The Max/Min can be in any column or row so the v look up needs to be dynamic or be able to look up the max/min in a range... please please help.....much apprecitaed |
| All times are GMT +1. The time now is 02:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com