more about vlookup....

Make that

=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$16,1)),VLOOKUP(A1,Sheet2!$A$1:$C$16,3,1),"")

You can also name the ranges of interest and use these names in the above formula instead of the ranges they refer to. if you'd like to do that: (1) Select A1:A16 on Sheet2, go to the Name Box and type e.g., LVALUES (from lookup values); (2) Select A1:C16 on Sheet2, go to the Name Box and type e.g., LTABLE (from lookup table). Now you can rewrite the formula as:

=IF(ISNUMBER(MATCH(A1,LVALUES,1)),VLOOKUP(A1,LTABLE,3,1),"")

Aladin

===============

Inside Aladin ==>

At first your formula works fine, but when I make slight change
to the lookup range, it shows nothing although
i put marks. any ideas. anyway, thanks

: =VLOOKUP(A1,Sheet2!$A$1:$C$16,3,1) 'this is in sheet1 : i got to enter data in Sheet1 while the vlookup range is : in Sheet2. i saw your post(aladin) about solving the vlookup : formula. that formula ok when the vlookup range is in the : same sheet with the data required. i have problem because : my data and the vlookup range is in the different sheets. please : help me. i want to avoid the #N/A result when there is no : data entered.thanks : below is your formula that works only when data and vlookup : range is in the same sheet. but when different sheets.it shows : nothing =IF(ISNUMBER(MATCH(Z6,$A$10:$A$14,1)),VLOOKUP(Z6,$A$10:$C$14,3,1),"")

Re: Inside Aladin ==>

Care to post all values of the lookup table along with the lookup value(s) for which the formula shows "nothing"?

Aladin

At first your formula works fine, but when I make slight change to the lookup range, it shows nothing although i put marks. any ideas. anyway, thanks Make that =IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$16,1)),VLOOKUP(A1,Sheet2!$A$1:$C$16,3,1),"") You can also name the ranges of interest and use these names in the above formula instead of the ranges they refer to. if you'd like to do that: (1) Select A1:A16 on Sheet2, go to the Name Box and type e.g., LVALUES (from lookup values); (2) Select A1:C16 on Sheet2, go to the Name Box and type e.g., LTABLE (from lookup table). Now you can rewrite the formula as: =IF(ISNUMBER(MATCH(A1,LVALUES,1)),VLOOKUP(A1,LTABLE,3,1),"") Aladin ===============

The 1st arg of MATCH

Having off-bord info on your problem, I can say that you forgat to adjust the MATCH part of the formula when you extended the lookup table. Given the formula below,

=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$16,1)),VLOOKUP(A1,Sheet2!$A$1:$C$16,3,1),"")

and you extend the lookup table which becomes say A1:C20, the following adjustments are required:

=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$20,1)),VLOOKUP(A1,Sheet2!$A$1:$C$20,3,1),"")

For MATCH, it must always a 1-column table which is the 1st column of your lookup table. That is: Sheet2!$A$1:$A$20 and NOT Sheet2!$A$1:$C$20.

Aladin

Care to post all values of the lookup table along with the lookup value(s) for which the formula shows "nothing"?

Re: The 1st arg of MATCH I've got it.thanks again Aladin

: Care to post all values of the lookup table along with the lookup value(s) for which the formula shows "nothing"? Aladin