vlookup list

[Back2Basics]

I am using a vlookup formula to a series of data sets. I am trying to get something so that it will display the relevant score for that site. However the lists are not the same size, how do i get a vlookup to read a score for a site if it is the the array but display "0" if the site name is not in the array?

It is coming up with #N/A and I can't do anything with that....

[sweep]

Hi,

You could use the error to return 0 by using ISERROR

=if(iserror(vlookup(lookup_value,table_array,col_index_num,range_lookup)),0,vlookup(lookup_value,table_array,col_index_num,range_lookup))

[DonkeyOte]

however it is better to only conduct the MATCH element rather than repeat the entire VLOOKUP, eg:

=IF(ISNA(MATCH(lookup_value,lookup_array,0)),0,VLOOKUP(criteria,table_array,col_index_num,range_lookup))

[NBVC]

and as your friend, AA, would suggest.. it is also better to look for positive results first...

e.g. =IF(ISNUMBER(MATCH(lookup_value,lookup_array,0)),VLOOKUP(criteria,table_array,col_index_num,range_look up),0)

[DonkeyOte]

he's not always right

(ok, yes he is... well alright 99.9% of the time... though I guess Ron would argue otherwise based on our little INDEX Volatile chat the other day)

[Back2Basics]

Thanks for the help guys - but it doesn't quite work.

I have attached the spreadheet with what I have done and the suggested formula by NBVC - but it just comes out as 0 for all of them.

[NBVC]

And also a good reason why it is always best to at least post the formula you currently have, so, at minimum, we can match up the references to ranges and sheets, so that most of the time all you need to do is copy/paste our formulas... or, at minimum, see how we incorporate your data references into our formulas.