Adding an IF function to the current formula?

[chaskin]

I am using the current formula to automatically find value for a particular name and add them together to find the average. =(VLOOKUP(A1,Sheet1!A:D,4,0)+INDEX(Sheet2!D:D,MATCH(A1,Sheet2!A:A,0)))/2

This final number gets imported to the sheet titled "Combined"

When the name being looked up is present in both sheet 1 and sheet 2, this formula works flawlessly. The issue I have is not every name appears in both sheets. This returns a value of #N/A. I am wondering if there is another function that could be added so that if the name only appears in either sheet 1 or sheet 2, but not both, the formula would simply return the value in the sheet where the name does appear. If the name does not appear in either sheet, I am ok with it returning #N/A as I can easily filter those out of the workbook.

I have attached the worksheet for reference. Thanks in advance!

[sandy666]

is this what you want?

Column1	Column2	Column3	AVG
Aaron Gordon	DEN	DEN	13
Aaron Holiday	ATL	ATL	9.3
Al Horford	BOS	BOS	12.4
Anfernee Simons	POR	POR	22.45
Anthony Edwards	MIN	MIN	22.45
Ayo Dosunmu	CHI	CHI	19.3
Bennedict Mathurin	IND	IND	22.1
Bojan Bogdanovic	DET	DET	15.3
Bradley Beal	WAS	WAS	21.6
Brook Lopez	MIL	MIL	13.2
Buddy Hield	IND	IND	15.55
Cade Cunningham	DET	DET	16.9
Cam Reddish	NY	NY	10.2
Cameron Johnson	PHO	PHO	15.3
Anthony Davis	LAL	LAL	22.2

[chaskin]

Yes that is correct. I just realized it did not include the actual name I am needing help with, so I have updated the workbook to include more detail below.

I have highlighted a name under the combined tab. This name appears in sheet 2 but not in sheet 1. it is still #N/A but what I'd like is for the formula to pull in data that is present is sheet 2 but not in sheet 1 as well.

What you provided is exactly what I need, I just need to make sure the formula pulls in names and data present in sheet 2 but not in sheet 1 as well.

Hope this makes sense.

[sandy666]

like this?

					Column1	Column2	Column3	AVG
Aaron Gordon	DEN	DEN	13		Aaron Gordon	DEN	DEN	13
Aaron Holiday	ATL	ATL	#N/A		Aaron Holiday	ATL	ATL	9.3
Al Horford	BOS	BOS	#N/A		Al Horford	BOS	BOS	12.4
Anfernee Simons	POR	POR	22.45		Anfernee Simons	POR	POR	22.45
Anthony Davis	LAL	LAL	#N/A		Anthony Davis	LAL	LAL	22.2
Anthony Edwards	MIN	MIN	22.45		Anthony Edwards	MIN	MIN	22.45
Ayo Dosunmu	CHI	CHI	#N/A		Ayo Dosunmu	CHI	CHI	19.3
Bennedict Mathurin	IND	IND	#N/A		Bennedict Mathurin	IND	IND	22.1
Bojan Bogdanovic	DET	DET	#N/A		Bojan Bogdanovic	DET	DET	15.3
Bradley Beal	WAS	WAS	21.6		Bradley Beal	WAS	WAS	21.6
Brook Lopez	MIL	MIL	#N/A		Brook Lopez	MIL	MIL	13.2
Buddy Hield	IND	IND	15.55		Buddy Hield	IND	IND	15.55
Cade Cunningham	DET	DET	16.9		Cade Cunningham	DET	DET	16.9
Cam Reddish	NY	NY	#N/A		Cam Reddish	NY	NY	10.2
Cameron Johnson	PHO	PHO	#N/A		Cameron Johnson	PHO	PHO	15.3

[chaskin]

Yes that is the result I am looking for. What formula did you use to obtain that?

[sandy666]

this is not a formula but Power Query, simple and fast

Please Login or Register  to view this content.