I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:

I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

Hi Claude

As below

=VLOOKUP(A30,AUTHOR,2)


"claude jerry" wrote:

give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:

I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

Formula seems correct to me provided two things
1. Your Array name "Author" is correct test it by typing Author in the name
box and press enter, and check if all the cell in which your Data is present
get selected.

2. You are entering A or B or C or D in Cell A30

Try this =Vlookup(A30,range of cell that contain author,2)

by Range of cell I mean First Cell : Last cell e.g (A1:B4)

So your Formula should look like =vlookup(A30,A1:B4,2)



"K11ngy" wrote:

Hi Claude

As below

=VLOOKUP(A30,AUTHOR,2)


"claude jerry" wrote:

give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:

I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

If "Title" does not match exactly, you will get #N/A error: extra blanks
could cause this.

"claude jerry" wrote:

Formula seems correct to me provided two things
1. Your Array name "Author" is correct test it by typing Author in the name
box and press enter, and check if all the cell in which your Data is present
get selected.

2. You are entering A or B or C or D in Cell A30

Try this =Vlookup(A30,range of cell that contain author,2)

by Range of cell I mean First Cell : Last cell e.g (A1:B4)

So your Formula should look like =vlookup(A30,A1:B4,2)



"K11ngy" wrote:

Hi Claude

As below

=VLOOKUP(A30,AUTHOR,2)


"claude jerry" wrote:

give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:

I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

Hi all -again
Well, I have tried everything

Last formula is below and also the tables bit no luck so far?

=VLOOKUP(E20,author,2)

Title result Title Author
A #N/A A Jordan
B B morate
C C Jordan
D D Jordan
E E surbin
F F surbin
G G morate
H H morate
I I morate
J J morate




"claude jerry" wrote:

give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:

I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

Ok lets work it out this way. open a New worksheet and do the following

1. In A1 type Title, In B1 Type Author

2. Enter your Data for tiltle i.e. A, B, C, D, E etc etc below Title
3. Enter your data for Author below Author
4. Lets asume you have so far entered Data in the following Range A1:B6

5. Lets keep cell D1, for the user to enter the Code (i.e. A or B or C or
D....)
6. IN Cell E1 Type the Following =Vlookup(D1,$A$2:$B$6,2)
7. Enter the Code (i.e. A or B or C or D....) in Cell D1
Is it working ?




"K11ngy" wrote:

Hi all -again
Well, I have tried everything

Last formula is below and also the tables bit no luck so far?

=VLOOKUP(E20,author,2)

Title result Title Author
A #N/A A Jordan
B B morate
C C Jordan
D D Jordan
E E surbin
F F surbin
G G morate
H H morate
I I morate
J J morate




"claude jerry" wrote:

give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:

I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

Hi Claude, thats superb but cant understand why it works when you told me
exactly same way as I have been doing. the only thing I did not do was start
a new work sheet and have been working on existing sheet (With data in
already) Can I also assume that data doesnt have to start in A1 ?

Anyway thanks vm for your help Claude

Appreciated

Steve


"claude jerry" wrote:

Ok lets work it out this way. open a New worksheet and do the following

1. In A1 type Title, In B1 Type Author

2. Enter your Data for tiltle i.e. A, B, C, D, E etc etc below Title
3. Enter your data for Author below Author
4. Lets asume you have so far entered Data in the following Range A1:B6

5. Lets keep cell D1, for the user to enter the Code (i.e. A or B or C or
D....)
6. IN Cell E1 Type the Following =Vlookup(D1,$A$2:$B$6,2)
7. Enter the Code (i.e. A or B or C or D....) in Cell D1
Is it working ?




"K11ngy" wrote:

Hi all -again
Well, I have tried everything

Last formula is below and also the tables bit no luck so far?

=VLOOKUP(E20,author,2)

Title result Title Author
A #N/A A Jordan
B B morate
C C Jordan
D D Jordan
E E surbin
F F surbin
G G morate
H H morate
I I morate
J J morate




"claude jerry" wrote:

give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:

I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve

Did you create a range named Author, that included all the cells with
titles and author names?
If not, the Author reference in your formula: =VLOOKUP(E20,Author,2)
would refer to the label of the column with the heading Author.
The title isn't found in that column, so the result is #N/A

It's best to avoid using labels in formulas, and use named ranges or
cell references instead. There are instructions here for naming a range:

http://www.contextures.com/xlNames01.html

and a few examples of VLOOKUP formulas:

http://www.contextures.com/xlFunctions02.html

K11ngy wrote:
Hi Claude, thats superb but cant understand why it works when you told me
exactly same way as I have been doing. the only thing I did not do was start
a new work sheet and have been working on existing sheet (With data in
already) Can I also assume that data doesnt have to start in A1 ?

Anyway thanks vm for your help Claude

Appreciated

Steve


"claude jerry" wrote:


Ok lets work it out this way. open a New worksheet and do the following

1. In A1 type Title, In B1 Type Author

2. Enter your Data for tiltle i.e. A, B, C, D, E etc etc below Title
3. Enter your data for Author below Author
4. Lets asume you have so far entered Data in the following Range A1:B6

5. Lets keep cell D1, for the user to enter the Code (i.e. A or B or C or
D....)
6. IN Cell E1 Type the Following =Vlookup(D1,$A$2:$B$6,2)
7. Enter the Code (i.e. A or B or C or D....) in Cell D1
Is it working ?




"K11ngy" wrote:


Hi all -again
Well, I have tried everything

Last formula is below and also the tables bit no luck so far?

=VLOOKUP(E20,author,2)

Title result Title Author
A #N/A A Jordan
B B morate
C C Jordan
D D Jordan
E E surbin
F F surbin
G G morate
H H morate
I I morate
J J morate




"claude jerry" wrote:


give us your formula, (the formula you have in your cell which shows #NA)

"K11ngy" wrote:


I am not getting any result on following formula and need help, all I get is
#N/A

I have the following information

Array table as below

Title Author
A SURBIN
B KING
C KING
D SURBIN


And so on

In the column I want the user to type in say "A" and it will look in array
table and bring back Surbin, same for "D" etc etc

I have sorted A-Z on Title and also tried same with Author but no success


help??

Steve




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html