vlookup and IF function
 

I am trying to use vlookup and nested IF functions, but cannot get it to
work-maybe I am not using the right funtions. I have a number that I want to
look up and if it is not found, I want to add 1 to it and look that up. I
only need to do it about 5 times so I thought if I used vlookup & nested IF's
I could get it. Here is example:

A
1 2
2 5
3 6
4 7
I want to start with looking up 3 and if it is there, return 3 but if it
isn't, add 1 to the 3 and look it up and return it, and if it isn't add 2 to
the 3, look it up and return it and if it isn't, return 0.

Any ideas?

Hi
if your data is sorted ascending try:
=VLOOKUP(3,A1:A20,1,TRUE)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag ...
I am trying to use vlookup and nested IF functions, but cannot get it
to
work-maybe I am not using the right funtions. I have a number that I
want to
look up and if it is not found, I want to add 1 to it and look that
up. I
only need to do it about 5 times so I thought if I used vlookup &
nested IF's
I could get it. Here is example:

A
1 2
2 5
3 6
4 7
I want to start with looking up 3 and if it is there, return 3 but if
it
isn't, add 1 to the 3 and look it up and return it, and if it isn't
add 2 to
the 3, look it up and return it and if it isn't, return 0.

Any ideas?

That is the basic formula for vlookup, but if it doesn't find 3 in the data,
I want to add 1 to the 3 and have it look for 4. This is what I have tried
and it doesn't totally work:
=if(vlookup($d3,a1:a20,1,false)=d3,d3,if(vlookup(( $d3+1),a1:a20,1,false)=(d3+1),(d3+1),0)

This formula actually works but if I want to nest another IF statement for
d3+2, it tells me I have too many arguements. I would like to nest up to 5
times.

"Frank Kabel" wrote:

Hi
if your data is sorted ascending try:
=VLOOKUP(3,A1:A20,1,TRUE)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag ...
I am trying to use vlookup and nested IF functions, but cannot get it
to
work-maybe I am not using the right funtions. I have a number that I
want to
look up and if it is not found, I want to add 1 to it and look that
up. I
only need to do it about 5 times so I thought if I used vlookup &
nested IF's
I could get it. Here is example:

A
1 2
2 5
3 6
4 7
I want to start with looking up 3 and if it is there, return 3 but if
it
isn't, add 1 to the 3 and look it up and return it, and if it isn't
add 2 to
the 3, look it up and return it and if it isn't, return 0.

Any ideas?

Hi
try the formula. It should find the value that is equal or larger than
3 (if your data is sorted ascending)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag ...
That is the basic formula for vlookup, but if it doesn't find 3 in
the data,
I want to add 1 to the 3 and have it look for 4. This is what I have
tried
and it doesn't totally work:

=if(vlookup($d3,a1:a20,1,false)=d3,d3,if(vlookup(( $d3+1),a1:a20,1,false
)=(d3+1),(d3+1),0)

This formula actually works but if I want to nest another IF
statement for
d3+2, it tells me I have too many arguements. I would like to nest
up to 5
times.

"Frank Kabel" wrote:

Hi
if your data is sorted ascending try:
=VLOOKUP(3,A1:A20,1,TRUE)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag
...
I am trying to use vlookup and nested IF functions, but cannot
get it
to
work-maybe I am not using the right funtions. I have a number
that I
want to
look up and if it is not found, I want to add 1 to it and look
that
up. I
only need to do it about 5 times so I thought if I used vlookup &
nested IF's
I could get it. Here is example:

A
1 2
2 5
3 6
4 7
I want to start with looking up 3 and if it is there, return 3
but if
it
isn't, add 1 to the 3 and look it up and return it, and if it
isn't
add 2 to
the 3, look it up and return it and if it isn't, return 0.

Any ideas?

This does not work or I am doing something wrong. Let's start over. This is
what I have:
A
1 3
2 4
3 6
4 7
5 9

In cell C1, I want to look up 1, and if it doesn't find 1, add 1 to the 1
for 2 and lookup 2, if it finds it, return it and if it doesn't find it, add
2 to the 1 and look up 3, if it finds 3, return 3 otherwise add 3 to the 1
and continue this up to 5 times until it does find a number. And then in C2,
I want it to take the value in C1, add 1 to it, look for it and if it doesn't
find it, add 2 to it and look for and continue this on for up to 5 times
until it finds the number. The formula you sent is a basic vlookup that will
not do this unless I am totally missing something here. There are gaps in
the numbers I am looking in and this is why I am doing this and I think this
is why your formula will not work.
"Frank Kabel" wrote:

Hi
try the formula. It should find the value that is equal or larger than
3 (if your data is sorted ascending)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag ...
That is the basic formula for vlookup, but if it doesn't find 3 in
the data,
I want to add 1 to the 3 and have it look for 4. This is what I have
tried
and it doesn't totally work:

=if(vlookup($d3,a1:a20,1,false)=d3,d3,if(vlookup(( $d3+1),a1:a20,1,false
)=(d3+1),(d3+1),0)

This formula actually works but if I want to nest another IF
statement for
d3+2, it tells me I have too many arguements. I would like to nest
up to 5
times.

"Frank Kabel" wrote:

Hi
if your data is sorted ascending try:
=VLOOKUP(3,A1:A20,1,TRUE)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag
...
I am trying to use vlookup and nested IF functions, but cannot
get it
to
work-maybe I am not using the right funtions. I have a number
that I
want to
look up and if it is not found, I want to add 1 to it and look
that
up. I
only need to do it about 5 times so I thought if I used vlookup &
nested IF's
I could get it. Here is example:

A
1 2
2 5
3 6
4 7
I want to start with looking up 3 and if it is there, return 3
but if
it
isn't, add 1 to the 3 and look it up and return it, and if it
isn't
add 2 to
the 3, look it up and return it and if it isn't, return 0.

Any ideas?

Hi
now I see :-)
in C1 try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A100,MATCH(1,(A1:A100=1)*(A1:A100<=6),0 ))

in C2 enter:
=INDEX(A1:A100,MATCH(1,(A1:A100=C1+1)*(A1:A100<=C 1+5),0))

Still requires the data to be sorted ascending

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag ...
This does not work or I am doing something wrong. Let's start over.
This is
what I have:
A
1 3
2 4
3 6
4 7
5 9

In cell C1, I want to look up 1, and if it doesn't find 1, add 1 to
the 1
for 2 and lookup 2, if it finds it, return it and if it doesn't find
it, add
2 to the 1 and look up 3, if it finds 3, return 3 otherwise add 3 to
the 1
and continue this up to 5 times until it does find a number. And then
in C2,
I want it to take the value in C1, add 1 to it, look for it and if it
doesn't
find it, add 2 to it and look for and continue this on for up to 5
times
until it finds the number. The formula you sent is a basic vlookup
that will
not do this unless I am totally missing something here. There are
gaps in
the numbers I am looking in and this is why I am doing this and I
think this
is why your formula will not work.
"Frank Kabel" wrote:

Hi
try the formula. It should find the value that is equal or larger
than
3 (if your data is sorted ascending)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag
...
That is the basic formula for vlookup, but if it doesn't find 3
in
the data,
I want to add 1 to the 3 and have it look for 4. This is what I
have
tried
and it doesn't totally work:


=if(vlookup($d3,a1:a20,1,false)=d3,d3,if(vlookup(( $d3+1),a1:a20,1,false
)=(d3+1),(d3+1),0)

This formula actually works but if I want to nest another IF
statement for
d3+2, it tells me I have too many arguements. I would like to
nest
up to 5
times.

"Frank Kabel" wrote:

Hi
if your data is sorted ascending try:
=VLOOKUP(3,A1:A20,1,TRUE)

--
Regards
Frank Kabel
Frankfurt, Germany

"cambridge" schrieb im
Newsbeitrag
...
I am trying to use vlookup and nested IF functions, but
cannot
get it
to
work-maybe I am not using the right funtions. I have a
number
that I
want to
look up and if it is not found, I want to add 1 to it and
look
that
up. I
only need to do it about 5 times so I thought if I used
vlookup &
nested IF's
I could get it. Here is example:

A
1 2
2 5
3 6
4 7
I want to start with looking up 3 and if it is there, return
3
but if
it
isn't, add 1 to the 3 and look it up and return it, and if it
isn't
add 2 to
the 3, look it up and return it and if it isn't, return 0.

Any ideas?