vlookup

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Claude

As below

=VLOOKUP(A30,AUTHOR,2)

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

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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 ?

 

[Guest]

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

 

[Debra Dalgleish]

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