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?

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.

=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?

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?

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?

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?

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?

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?

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?