Hi, I wonder,how can i obtain these below sample data from my list and put
them in specified cells:
A B C .............................. H I
1 . . . 10 1
2 10 1 . 10 2
3 . . . 10 3
4 . 10 2
5 . . .
6 . 10 3
7 . . .
and if not find any thing replace " " insted of N/A error in its last vlookup
Thanks in advance

"Wrap" your VLOOKUP or HLOOKUP within an ISNA() function. General format
would be like this:

=IF(ISNA(VLOOKUP(10,A1:C7,2),"",VLOOKUP(10,A1:C7,2 ))


"bijan" wrote:

Hi, I wonder,how can i obtain these below sample data from my list and put
them in specified cells:
A B C .............................. H I
1 . . . 10 1
2 10 1 . 10 2
3 . . . 10 3
4 . 10 2
5 . . .
6 . 10 3
7 . . .
and if not find any thing replace " " insted of N/A error in its last vlookup
Thanks in advance

Hi,thanks for your help
I realy don't know vlookup or hlookup is helpful but important thing is that
I want
find "10"s and thier couresponds value in the next or maybe other culumns
from up to down the list
and put them in range H:I as sample, and at end of search don't return N/A
error.
your recommend is so helpful


"JLatham" wrote:

"Wrap" your VLOOKUP or HLOOKUP within an ISNA() function. General format
would be like this:

=IF(ISNA(VLOOKUP(10,A1:C7,2),"",VLOOKUP(10,A1:C7,2 ))


"bijan" wrote:

Hi, I wonder,how can i obtain these below sample data from my list and put
them in specified cells:
A B C .............................. H I
1 . . . 10 1
2 10 1 . 10 2
3 . . . 10 3
4 . 10 2
5 . . .
6 . 10 3
7 . . .
and if not find any thing replace " " insted of N/A error in its last vlookup
Thanks in advance

Sorry, I thought you were looking at VLOOKUP() specifically. You are
correct, it is not part of the total solution here for two reasons:
#1 - you need to look in two separate lookup lists: A1:B7 and also in B1:C7
and even a combined VLOOKUP such as
=IF(ISNA(VLOOKUP(10,A$1:B$7,2)),IF(ISNA(VLOOKUP(10 ,B$1:C$7,2)),"not
found",VLOOKUP(10,B$1:C$7,2)),VLOOKUP(10,A$1:B$7,2 ))
is always going to stop at the first match in the first lookup range (A1:B7)

#2 - you have duplicate values to find in at least one of the groups [ 10..2
and 10..3] and VLOOKUP will again stop at the first match.

There is a way to deal with this, I've seen it, and I've just got to find
where it's been done before and repeat it here. Let me think on it a while,
call in some heavy weight help, and/or hope someone who's done it recently
steps in and shows us how to do it again.


"bijan" wrote:

Hi,thanks for your help
I realy don't know vlookup or hlookup is helpful but important thing is that
I want
find "10"s and thier couresponds value in the next or maybe other culumns
from up to down the list
and put them in range H:I as sample, and at end of search don't return N/A
error.
your recommend is so helpful


"JLatham" wrote:

"Wrap" your VLOOKUP or HLOOKUP within an ISNA() function. General format
would be like this:

=IF(ISNA(VLOOKUP(10,A1:C7,2),"",VLOOKUP(10,A1:C7,2 ))


"bijan" wrote:

Hi, I wonder,how can i obtain these below sample data from my list and put
them in specified cells:
A B C .............................. H I
1 . . . 10 1
2 10 1 . 10 2
3 . . . 10 3
4 . 10 2
5 . . .
6 . 10 3
7 . . .
and if not find any thing replace " " insted of N/A error in its last vlookup
Thanks in advance

Need more definition of the "rules".

Will there always be a 10 and at least one other number?

Is this a possibility:

.........A......B......C
1.......................10
2..............10.........
3......10....10.........
4......10....10.....10

Will the 10's *only" be in either column A or column B?

Is your "real" data only 3 columns?

Biff

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Sorry, I thought you were looking at VLOOKUP() specifically. You are
correct, it is not part of the total solution here for two reasons:
#1 - you need to look in two separate lookup lists: A1:B7 and also in
B1:C7
and even a combined VLOOKUP such as
=IF(ISNA(VLOOKUP(10,A$1:B$7,2)),IF(ISNA(VLOOKUP(10 ,B$1:C$7,2)),"not
found",VLOOKUP(10,B$1:C$7,2)),VLOOKUP(10,A$1:B$7,2 ))
is always going to stop at the first match in the first lookup range
(A1:B7)

#2 - you have duplicate values to find in at least one of the groups [
10..2
and 10..3] and VLOOKUP will again stop at the first match.

There is a way to deal with this, I've seen it, and I've just got to find
where it's been done before and repeat it here. Let me think on it a
while,
call in some heavy weight help, and/or hope someone who's done it recently
steps in and shows us how to do it again.


"bijan" wrote:

Hi,thanks for your help
I realy don't know vlookup or hlookup is helpful but important thing is
that
I want
find "10"s and thier couresponds value in the next or maybe other culumns
from up to down the list
and put them in range H:I as sample, and at end of search don't return
N/A
error.
your recommend is so helpful


"JLatham" wrote:

"Wrap" your VLOOKUP or HLOOKUP within an ISNA() function. General
format
would be like this:

=IF(ISNA(VLOOKUP(10,A1:C7,2),"",VLOOKUP(10,A1:C7,2 ))


"bijan" wrote:

Hi, I wonder,how can i obtain these below sample data from my list
and put
them in specified cells:
A B C .............................. H I
1 . . . 10 1
2 10 1 . 10 2
3 . . . 10 3
4 . 10 2
5 . . .
6 . 10 3
7 . . .
and if not find any thing replace " " insted of N/A error in its last
vlookup
Thanks in advance