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)),"",V LOOKUP(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)),"",V LOOKUP(A2,CrewList!$k$9:$C$33,2,0))

Please help

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.

--
Biff
Microsoft Excel MVP


"Udayan" wrote in message
...
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)),"",V LOOKUP(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)),"",V LOOKUP(A2,CrewList!$k$9:$C$33,2,0))

Please help

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)),"",VL OOKUP(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.''




"Udayan" wrote:

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)),"",V LOOKUP(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)),"",V LOOKUP(A2,CrewList!$k$9:$C$33,2,0))

Please help

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.


"Udayan" wrote:

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)),"",V LOOKUP(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)),"",V LOOKUP(A2,CrewList!$k$9:$C$33,2,0))

Please help

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) )

--
Biff
Microsoft Excel MVP


"Udayan" < VLOOKUP wrote in message
...
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.


"Udayan" wrote:

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)),"",V LOOKUP(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)),"",V LOOKUP(A2,CrewList!$k$9:$C$33,2,0))

Please help