i want to find a value in two different sheets with one formula

Try:

Please Login or Register  to view this content.

When Vlookup() function does not find a match, it returns a #N/A error. So the first part of the formula checks if there is no match found in Sheet1... that's with the Isna() function (which means is the result N/A?). If it is not found (or N/A), then it will perform the 2nd part (which is to look in Sheet2), otherwise it should be in Sheet1 so it will execute the 3rd part and return that.... if both sheets have no matches, you will get the #N/A error.

Note: there are other ways of writing the formula that may be more efficient... one of the best ways is as follows:

=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,FALSE)),VLOOKUP(A1,sheet1!A:B,2,FALSE),VLOOKUP(A1,Sheet2!A:B,2,FALSE))

which is similar...except the Match() function returns a number representing the position of the match it finds in the range Sheet1!A:A. If it finds a match, then the ISNUMBER() validates and returns TRUE.. which forces the Vlookup in Sheet1 (ie. the 2nd part of the IF() formula. If Isnumber() returns FALSE (i.e. no match found), then it goes to the 3rd part of the formula to lookup Sheet2.

Hope this all helps.