IF AND or INDEX/MATCH?

[JoshLyman]

I'm trying to write a formula which will tell me if the selection in cell B2 appears in the "Cohort Ref" column on Sheet1, and if so, whether the value in the 'Actual Recruitment' column on Sheet1 is greater than 0. If it is, I need it to return Y, but if it isn't I need it to return N:

Initially I tried =IF(AND(B2=Table2[Cohort Ref.],Table2[Actual Recruitment]>0),"Y","N") but this only works if there's 1 line in the table on Sheet1. If there are multiple, it always returns N. I suspect I need to probably use MATCH and/or INDEX, but my experience with those is very limited!

Thanks in advance

 

[etaf]

or a sumifs()
but i cannot reconcile the images

I can see we would
sumifs( column E, column C , cell with dropdown (HAD 08) )
then an IF
IF( sumifs( column E, column C , cell with dropdown (HAD 08) ) >0, "Y", "N")

but i cannot see why that would be entered in the cell on image 1

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[JoshLyman]

Hopefully this works

Book1.xlsx
	A	B	C	D	E	F
1	Please choose your cohort		Has this task been completed?		Cohort	Recruitment figure
2	HAD 08		N		HAD 08	0
3					HAD 09	14
4					HAD 10	25
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(AND(A2=Table1[Cohort],Table1[Recruitment figure]>0),"Y","N")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet2!$A$1:$A$3

A2 is going to be changed constantly, depending on who uses the sheet and which cohort they want to check on.
C2 is where my formula needs to go, and needs to check whether the recruitment figure for the cohort selected in A2 is greater than 0. If it is, it should return Y, if it isn't, it should return N

 

[etaf]

need both sheets -
but a sumifs() should do that for you

Sheet 2

Book1
	A	B	C	D	E
1			cohort		recruitment
2			HAD 08		0
3			HAD 09		14
4			HAD 10		25
Sheet2

Sheet1 - using sumifs

Book1
	A	B	C	D	E	F
1	Please choose your cohort		Has this task been completed?		Cohort	Recruitment figure
2	HAD 08		N		HAD 08	0
3	HAD 09		Y		HAD 09	14
4	HAD 10		Y		HAD 10	25
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(SUMIFS(Sheet2!$E$2:$E$4,Sheet2!$C$2:$C$4,Sheet1!A2)>0,"Y","N")
F2:F4	F2	=SUMIFS(Sheet2!$E$2:$E$4,Sheet2!$C$2:$C$4,Sheet1!A2)

on dropbox

lookup Cohort Y or N -ETAF.xlsx

Shared with Dropbox

www.dropbox.com

will only be kept for a few days

 

[AlanY]

this will show "N" if not found in Column Cohort

Book1
	A	B	C	D	E	F
1	Please choose your cohort		Has this task been completed?		Cohort	Recruitment figure
2	HAD 08		N		HAD 08	0
3	HAD 09		Y		HAD 09	14
4	HAD 10		Y		HAD 10	25
5	HAD 11		N
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(IF(INDEX(F:F,MATCH($A2,E:E,0))>0,"Y","N"),"N")

 

[JoshLyman]

Both work! Thank you etaf and AlanY!

 

[AlanY]

Both work! Thank you etaf and AlanY!

you're welcome

 

[etaf]

you are welcome, glad you have a solution