Value if Lookup is False

[otta]

Hi everyone, I am at a very basic level of Excel and need some help please. I need to write a formula where the Range_lookup in a Vlookup DOES NOT return a #N/A if the value in Cell B2:B3 DOES NOT match A2. The Vlookup formula I currently use is =VLOOKUP(A2,B2:B3,1,FALSE), however if the value IS false, it returns a #N/A result. What I want the result to show is the actual text in B2:B3. Any help greatly appreciated.
Regards Otta.

 

[Jonmo1]

Welcome to the board...

Try

=IF(ISNA(MATCH(A2,B2:B3,0)),"",A2)


Hope that helps.

 

[T. Valko]

Hi everyone, I am at a very basic level of Excel and need some help please. I need to write a formula where the Range_lookup in a Vlookup DOES NOT return a #N/A if the value in Cell B2:B3 DOES NOT match A2. The Vlookup formula I currently use is =VLOOKUP(A2,B2:B3,1,FALSE), however if the value IS false, it returns a #N/A result. What I want the result to show is the actual text in B2:B3. Any help greatly appreciated.
Regards Otta.

If the value in cell A2 is not found in B2:B3 then the formula will return #N/A. If you do not want the formula to return that error then what result do you want instead?

What version of Excel are you using?

 

[otta]

Jon, thanks so much for the ultra quick response.

I tried that formula and can't seem to get it to work. This formula seems to return a "true" result, rather than the "actual data" located in the cell? is there a way that I can get the result to be the data in the cell rather than true/false value?

 

[Jonmo1]

The formula I posted will only return TRUE if the value in A2 is TRUE AND either B2 or B3 is also TRUE.

Can you post the exact formula as you have it now?

And perhaps post a sample screenshot, see my signature for a method to do so.

 

[otta]

Hi Valko, I am using Office 2010. Let me try and explain myself a bit clearer (sorry am tired). I have a workbook (Book 2) containing Employee ID in Column A (eg: 10125), Dept ID in Column C (eg: 284) and Address in Column F(eg: West St). I have done a concatenate function in Column G of the 3 to get a result of 10125284West St. There is another workbook (Book1) with data that has the same Employee ID (this is a constant), but may have a different DEPT ID and/or Address (The employee may have changed departments or relocated). So in Book 1 the same employee has Employee ID (10125), Dept ID (284), Address (East St). So in Book1 I have a concatenate value of 10125284East St. I want Book 2 to to show me the concatenate value if it is different to Book1.
Regards Otta.

 

[otta]

Hi all, I looked elsewhere on the forum, found this formula in closest match/lookup....... =vlookup(A1,B1:C10,2,1), exactly what I was after. Thank you all for responding.

 

[Aladin Akyurek]

Hi all, I looked elsewhere on the forum, found this formula in closest match/lookup....... =vlookup(A1,B1:C10,2,1), exactly what I was after. Thank you all for responding.

B1:C10 must be sorted in ascending order on B1:B10 though in order to use such a formula.

 

[T. Valko]

Hi all, I looked elsewhere on the forum, found this formula in closest match/lookup....... =vlookup(A1,B1:C10,2,1), exactly what I was after. Thank you all for responding.

Good deal. Thanks for the feedback!