|
LARGE & VLOOKUP
I am trying to use LARGE function with VLOOKUP function and except for the
case where k=1, I am getting a #NUM! message The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not stable, i.e. the number of arguments to compare with varies according to a text criteria. Thats why I am using the VLOOKUP So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1); 1;0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
LARGE & VLOOKUP
pateodoro wrote:
I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a #NUM! message The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not stable, i.e. the number of arguments to compare with varies according to a text criteria. Thats why I am using the VLOOKUP So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1); 1;0) Column F has numbers Column D has text Does anyone have an idea how to solve this? Your VLOOKUP is not valid. You are looking for the value in D12 in the range D12:F30. It will be found at D12. You probably want some other cell reference in place of the first D12. |
LARGE & VLOOKUP
=IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1) ;1;0)
For the nth number you need to compare it agaisnt other numbers. As is you're not doing that for anything other than the largest value which is a 1:1 comparison. If you're looking for the 2nd largest value then you need at least a 1:2 comparison. You need to replace VLOOKUP with something that returns an array of numbers. VLOOKUP returns just a single element which is why the LARGE(...,1) works. It's a 1:1 comparison. Need a more detailed explanation to figure out what to suggest. -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a "#NUM!" message. The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not "stable", i.e. the number of arguments to compare with varies according to a text criteria. That's why I am using the VLOOKUP. So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1); 1;0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
LARGE & VLOOKUP
A B C
1 Lisbon 25 2 Lisbon 13 3 Lisbon 5 4 Lisbon 95 5 Lisbon 4 6 Paris 100 7 Paris 23 8 Paris 45 This is my example and I want to find a way to simplify the calculations because I have a list of more than 1800 rows! I want to write a conditional function that result in something like this: on C1 I want to say that if B1 is the 1st or 2nd largest number of the Lisbon group (which has 5 rows) it should write 1, otherwise 0; on C2 I want to say that if B2 is the 1st or 2nd largest number of the Lisbon group (which has 5 rows) it should write 1, otherwise 0 and so on And I want to apply the same condition on Paris group which has 3 rows Thats why I was trying to use the VLOOKUP function I pretend to say that on the range of numbers associated to Lisbon please tell me if B1 is the 1st or 2nd largest number I am not sure if I explained myself quite well I tried to simplify the example Thanks anyway for your help! "T. Valko" escreveu: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1) ;1;0) For the nth number you need to compare it agaisnt other numbers. As is you're not doing that for anything other than the largest value which is a 1:1 comparison. If you're looking for the 2nd largest value then you need at least a 1:2 comparison. You need to replace VLOOKUP with something that returns an array of numbers. VLOOKUP returns just a single element which is why the LARGE(...,1) works. It's a 1:1 comparison. Need a more detailed explanation to figure out what to suggest. -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a "#NUM!" message. The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not "stable", i.e. the number of arguments to compare with varies according to a text criteria. That's why I am using the VLOOKUP. So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1); 1;0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
LARGE & VLOOKUP
I think something like this will do it, entered as an array formula with
Ctrl+Shift+Enter: =IF(OR(C1=LARGE(--(B1=$B$1:$B$8)*($C$1:$C$8),1),C1=LARGE(--(B1=$B$1:$B$8)*($C$1:$C$8),2)),1,0) pateodoro wrote: A B C 1 Lisbon 25 2 Lisbon 13 3 Lisbon 5 4 Lisbon 95 5 Lisbon 4 6 Paris 100 7 Paris 23 8 Paris 45 This is my example and I want to find a way to simplify the calculations because I have a list of more than 1800 rows! I want to write a conditional function that result in something like this: on C1 I want to say that if B1 is the 1st or 2nd largest number of the Lisbon group (which has 5 rows) it should write 1, otherwise 0, on C2 I want to say that if B2 is the 1st or 2nd largest number of the Lisbon group (which has 5 rows) it should write 1, otherwise 0 and so on And I want to apply the same condition on Paris group which has 3 rows Thats why I was trying to use the VLOOKUP function I pretend to say that on the range of numbers associated to Lisbon please tell me if B1 is the 1st or 2nd largest number I am not sure if I explained myself quite well I tried to simplify the example Thanks anyway for your help! "T. Valko" escreveu: =IF(F12=LARGE(VLOOKUP(D12,$D$12:$F$30,3,FALSE),1), 1,0) For the nth number you need to compare it agaisnt other numbers. As is you're not doing that for anything other than the largest value which is a 1:1 comparison. If you're looking for the 2nd largest value then you need at least a 1:2 comparison. You need to replace VLOOKUP with something that returns an array of numbers. VLOOKUP returns just a single element which is why the LARGE(...,1) works. It's a 1:1 comparison. Need a more detailed explanation to figure out what to suggest. -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a "#NUM!" message. The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not "stable", i.e. the number of arguments to compare with varies according to a text criteria. That's why I am using the VLOOKUP. So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12,$D$12:$F$30,3,FALSE),1), 1,0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
LARGE & VLOOKUP
Sorry, I got the column references wrong per your sample. Also, it
occurs to me the "--" were unnecessary, so here is an improved solution (again, an array formula): =IF(OR(B2=LARGE((A2=$A$2:$A$9)*($B$2:$B$9),1),B2=L ARGE((A2=$A$2:$A$9)*($B$2:$B$9),2)),1,0) smartin wrote: I think something like this will do it, entered as an array formula with Ctrl+Shift+Enter: =IF(OR(C1=LARGE(--(B1=$B$1:$B$8)*($C$1:$C$8),1),C1=LARGE(--(B1=$B$1:$B$8)*($C$1:$C$8),2)),1,0) pateodoro wrote: A B C 1 Lisbon 25 2 Lisbon 13 3 Lisbon 5 4 Lisbon 95 5 Lisbon 4 6 Paris 100 7 Paris 23 8 Paris 45 This is my example and I want to find a way to simplify the calculations because I have a list of more than 1800 rows! I want to write a conditional function that result in something like this: on C1 I want to say that if B1 is the 1st or 2nd largest number of the Lisbon group (which has 5 rows) it should write 1, otherwise 0, on C2 I want to say that if B2 is the 1st or 2nd largest number of the Lisbon group (which has 5 rows) it should write 1, otherwise 0 and so on And I want to apply the same condition on Paris group which has 3 rows Thats why I was trying to use the VLOOKUP function I pretend to say that on the range of numbers associated to Lisbon please tell me if B1 is the 1st or 2nd largest number I am not sure if I explained myself quite well I tried to simplify the example Thanks anyway for your help! "T. Valko" escreveu: =IF(F12=LARGE(VLOOKUP(D12,$D$12:$F$30,3,FALSE),1), 1,0) For the nth number you need to compare it agaisnt other numbers. As is you're not doing that for anything other than the largest value which is a 1:1 comparison. If you're looking for the 2nd largest value then you need at least a 1:2 comparison. You need to replace VLOOKUP with something that returns an array of numbers. VLOOKUP returns just a single element which is why the LARGE(...,1) works. It's a 1:1 comparison. Need a more detailed explanation to figure out what to suggest. -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a "#NUM!" message. The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not "stable", i.e. the number of arguments to compare with varies according to a text criteria. That's why I am using the VLOOKUP. So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12,$D$12:$F$30,3,FALSE),1), 1,0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
LARGE & VLOOKUP
Assuming there are at least 2 entries for each group...
Array entered** in C1: =--(B1=LARGE(IF(A$1:A$8=A1,B$1:B$8),2)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... A B C 1 Lisbon 25 2 Lisbon 13 3 Lisbon 5 4 Lisbon 95 5 Lisbon 4 6 Paris 100 7 Paris 23 8 Paris 45 This is my example - and I want to find a way to simplify the calculations because I have a list of more than 1800 rows! ? I want to write a conditional function that result in something like this: on C1 I want to say that if B1 is the 1st or 2nd largest number of the "Lisbon group" (which has 5 rows) it should write 1, otherwise 0; on C2 I want to say that if B2 is the 1st or 2nd largest number of the "Lisbon group" (which has 5 rows) it should write 1, otherwise 0. and so on. And I want to apply the same condition on "Paris group" which has 3 rows. That's why I was trying to use the VLOOKUP function - I pretend to "say" that on the range of numbers associated to Lisbon please tell me if B1 is the 1st or 2nd largest number. I am not sure if I explained myself quite well. I tried to simplify the example. Thanks anyway for your help! "T. Valko" escreveu: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1) ;1;0) For the nth number you need to compare it agaisnt other numbers. As is you're not doing that for anything other than the largest value which is a 1:1 comparison. If you're looking for the 2nd largest value then you need at least a 1:2 comparison. You need to replace VLOOKUP with something that returns an array of numbers. VLOOKUP returns just a single element which is why the LARGE(...,1) works. It's a 1:1 comparison. Need a more detailed explanation to figure out what to suggest. -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a "#NUM!" message. The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not "stable", i.e. the number of arguments to compare with varies according to a text criteria. That's why I am using the VLOOKUP. So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1); 1;0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
LARGE & VLOOKUP
Hi T. Valko,
Thanks a lot for your help! Your suggestion works perfectly! :-) Regards, Patricia "T. Valko" escreveu: Assuming there are at least 2 entries for each group... Array entered** in C1: =--(B1=LARGE(IF(A$1:A$8=A1,B$1:B$8),2)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... A B C 1 Lisbon 25 2 Lisbon 13 3 Lisbon 5 4 Lisbon 95 5 Lisbon 4 6 Paris 100 7 Paris 23 8 Paris 45 This is my example - and I want to find a way to simplify the calculations because I have a list of more than 1800 rows! ? I want to write a conditional function that result in something like this: on C1 I want to say that if B1 is the 1st or 2nd largest number of the "Lisbon group" (which has 5 rows) it should write 1, otherwise 0; on C2 I want to say that if B2 is the 1st or 2nd largest number of the "Lisbon group" (which has 5 rows) it should write 1, otherwise 0. and so on. And I want to apply the same condition on "Paris group" which has 3 rows. That's why I was trying to use the VLOOKUP function - I pretend to "say" that on the range of numbers associated to Lisbon please tell me if B1 is the 1st or 2nd largest number. I am not sure if I explained myself quite well. I tried to simplify the example. Thanks anyway for your help! "T. Valko" escreveu: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1) ;1;0) For the nth number you need to compare it agaisnt other numbers. As is you're not doing that for anything other than the largest value which is a 1:1 comparison. If you're looking for the 2nd largest value then you need at least a 1:2 comparison. You need to replace VLOOKUP with something that returns an array of numbers. VLOOKUP returns just a single element which is why the LARGE(...,1) works. It's a 1:1 comparison. Need a more detailed explanation to figure out what to suggest. -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a "#NUM!" message. The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not "stable", i.e. the number of arguments to compare with varies according to a text criteria. That's why I am using the VLOOKUP. So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1); 1;0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
LARGE & VLOOKUP
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "pateodoro" wrote in message ... Hi T. Valko, Thanks a lot for your help! Your suggestion works perfectly! :-) Regards, Patricia "T. Valko" escreveu: Assuming there are at least 2 entries for each group... Array entered** in C1: =--(B1=LARGE(IF(A$1:A$8=A1,B$1:B$8),2)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... A B C 1 Lisbon 25 2 Lisbon 13 3 Lisbon 5 4 Lisbon 95 5 Lisbon 4 6 Paris 100 7 Paris 23 8 Paris 45 This is my example - and I want to find a way to simplify the calculations because I have a list of more than 1800 rows! ? I want to write a conditional function that result in something like this: on C1 I want to say that if B1 is the 1st or 2nd largest number of the "Lisbon group" (which has 5 rows) it should write 1, otherwise 0; on C2 I want to say that if B2 is the 1st or 2nd largest number of the "Lisbon group" (which has 5 rows) it should write 1, otherwise 0. and so on. And I want to apply the same condition on "Paris group" which has 3 rows. That's why I was trying to use the VLOOKUP function - I pretend to "say" that on the range of numbers associated to Lisbon please tell me if B1 is the 1st or 2nd largest number. I am not sure if I explained myself quite well. I tried to simplify the example. Thanks anyway for your help! "T. Valko" escreveu: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1) ;1;0) For the nth number you need to compare it agaisnt other numbers. As is you're not doing that for anything other than the largest value which is a 1:1 comparison. If you're looking for the 2nd largest value then you need at least a 1:2 comparison. You need to replace VLOOKUP with something that returns an array of numbers. VLOOKUP returns just a single element which is why the LARGE(...,1) works. It's a 1:1 comparison. Need a more detailed explanation to figure out what to suggest. -- Biff Microsoft Excel MVP "pateodoro" wrote in message ... I am trying to use LARGE function with VLOOKUP function and except for the case where k=1, I am getting a "#NUM!" message. The idea is to use a conditional function (like IF) and the logical test is to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But the group of values is not "stable", i.e. the number of arguments to compare with varies according to a text criteria. That's why I am using the VLOOKUP. So, the formula I used is the following: =IF(F12=LARGE(VLOOKUP(D12;$D$12:$F$30;3;FALSE);1); 1;0) Column F has numbers Column D has text Does anyone have an idea how to solve this? |
| All times are GMT +1. The time now is 07:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com