VLOOKUP help in excel

Columbo · 30 Mar 2009 at 11:50

The are 2 tabs, tab 1 is running a vlookup against tab 2 and returning a value when it finds a match.

Screenshot below of sample data:

Is there a way to replace the '#N/A' with something that excel will recognise as a value when it does not find a match in tab 2.

I want to add up the number of positive return values that it finds.

Clear as mud I'm sure but any help appreciated.

kidloco · 30 Mar 2009 at 12:03

think you need to add the ISNA, here is one from one im currently working on

=IF(ISNA(VLOOKUP(A2,Cust,1,FALSE)),"NEW","Existing")

uv · 30 Mar 2009 at 12:06

not sure why my ISNA function is so much larger than yours

=IF(ISNA(VLOOKUP(A2, Sheet3!D:E, 2, FALSE)),".",VLOOKUP(A2, Sheet3!D:E, 2, FALSE))

The "." is for conditional formatting, so the box is greyed out.

Participant · 30 Mar 2009 at 12:38

Not saying the above two are wrong as I've never heard of that function, but I use:

=IF(ISERROR(VLOOKUP(A2,Sheet3!D:E,2,FALSE)=TRUE),0,VLOOKUP(A2,Sheet3!D:E,2,FALSE))

Columbo · 30 Mar 2009 at 12:39

Mighty helpful of you fellas.

Worked brilliantly, have some cookies on me..!!!

Columbo · 30 Mar 2009 at 15:46

Right chaps another one for ya.

Anyway to get that VLOOKUP in my first post to look across more than tab at once?

uv · 30 Mar 2009 at 16:45

Do you mean across work sheets in the same spreadsheet?

Is so, then user Sheetname!Cellrange, eg:

=vlookup(A, Sheet2!A:B, 2, false)