If value not found, display "--"

Question

I have a sheet (LC AVERAGE) that has a list of names and a column of values. I am looking to display the values on an OVERVIEW sheet that I have, but there are more names on the OVERVIEW sheet than on the LC AVERAGE sheet. I tried IFERROR and IF, but am getting 0 in cells where the name doesn't match, which is affecting other formulas on the OVERVIEW page.

=IFERROR((SUMIF('LC AVERAGE'!$A:$A,OVERVIEW!$B8,'LC AVERAGE'!$Z:$Z)),"--")

I basically need it to display the value in LC AVERAGE'!$Z:$Z on the overview page if the names in 'LC AVERAGE'!$A:$A match the names on OVERVIEW!$B8 (B column), and if the name doesn't exist, display --.

I think the issue is that it isn't counted as an error in Excel if the name simply isn't there, but I've searched and I can't get this to work.

Any thoughts?

Answer 1

Am not sure if there's a better way of getting the same thing, but here's something:

Column A    Column B   
A            =IF(ISERROR(MATCH(A1,D:D,0)),"--",SUMIF($D:$D,A1,E:E))
B            =IF(ISERROR(MATCH(A2,D:D,0)),"--",SUMIF($D:$D,A2,E:E))
C            =IF(ISERROR(MATCH(A3,D:D,0)),"--",SUMIF($D:$D,A3,E:E))

Where the data is in

Column D   Column E
A           5
B           10

Am just checking for the value in the data set using MATCH before it sums up the result.

Answer 2

I might not understand, but maybe detect that the value in OVERVIEW!$B8 does not exist in 'LC AVERAGE'!$A:$A as the condition for going to your SUMIF formula and otherwise returning --:

 =IF(COUNTIF('LC AVERAGE'!$A:$A,OVERVIEW!B8)>0,SUMIF('LC AVERAGE'!$A:$A,OVERVIEW!$B8,'LC AVERAGE'!$Z:$Z),"--")