VLOOKUP fORMULA

U

Udayan

dEAR aLL pLEASE hELP mE

In sheet1 Column C2 i have a formula which compare A2 value in sheet1 with
(B9) value in CrewList, if it is true return the value in C9.


=IF(ISNA(VLOOKUP(A2,CrewList!$B$9:$C$33,2,0)),"",VLOOKUP(A2,CrewList!$B$9:$C$33,2,0))

I just want to change instead of B9 to look up at K9


I changed the formula something like this and it is not working.
=IF(ISNA(VLOOKUP(A2,CrewList!$k$9:$C$33,2,0)),"",VLOOKUP(A2,CrewList!$k$9:$C$33,2,0))

Please help
 
T

T. Valko

I just want to change instead of B9 to look up at K9
VLOOKUP(A2,CrewList!$k$9:$C$33,2,0)

Even though you have the lookup table defined as K9:C33 Excel evaluates it
as C9:K33. So, the formula is looking for the lookup_value in C9:C33 and if
found returns the corresponding value from the 2nd column of the lookup
table which would be D9:D33.
 
G

GSnyder

Since you're returning the second column in the range (hence, the ,2,0), you
must really mean that you want to look up from columns K to L, not K to C.
Like this:

=IF(ISNA(VLOOKUP(A2,CrewList!$k$9:$L33,2,0)),"",VLOOKUP(A2,CrewList!$k$9:$L$33,2,0))

I guess what you're saying is that your list of information (the crew list)
is now in K9:L33 rather than from B9:C33, correct? Remember, the first thing
is what you're looking for (A2), the second is where you're looking for it
(Crewlist K9:L33), the third is what column you'd like to pull back (column
2, or the L column), and last is whether or not you want an exact match.

Just so you know, if you're in Excel 2007, you can now use the handy IFERROR
function and can shorten your formula to:

=IFERROR(VLOOKUP(A2,CrewList!$K$9:$L$33,2,0),"")

Happy calculating!

If you like this answer, please click ''Yes.''
 
U

Udayan

Let me explain the question again.

Looking for (A2) in Sheet1
The second is where you're looking for it (Crewlist K9:K33),
The third is what column I w'd like to pull back is C9
I Want an exact match.

At present I am using 2007 but I have to use this file in earlier version
also.
 
T

T. Valko

In other words, you want to do a right-to-left lookup?

If that's the case then you can't use VLOOKUP. Try this:

=INDEX(Crewlist!C9:C33,MATCH(A2,Crewlist!K9:K33,0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top