IF, OR, Index, Match, Multiple Criteria

[wintye]

Hi All,

I hope you're enjoying the weekend.

I wonder if anyone can help with the following formula. I'm trying to pull a date from another table but only if it matches certain criteria.

Table with Data

Unique Identifier	First Name	Last Name	Course	Date	Grade
12345	Jane	Doe	Course 1	15/04/2016	3
12345	Jane	Doe	Course 2	17/05/2017	3
12345	Jane	Doe	Course 3	19/01/2018	3
34567	John	Smith	Course 2	10/07/2017	5
34567	John	Smith	Course 3	25/06/2017	5
34567	John	Smith	Course 1	28/10/2016	5
78901	Jane	Smith	Course 3	01/01/2016	4
78901	Jane	Smith	Course 2	01/10/2017	4
78901	Jane	Smith	Course 1	01/05/2015	4
	John	Doe	Course 2	29/03/2015	7
	John	Doe	Course 1	23/10/2017	7
	John	Doe	Course 3	16/03/2016	7

<tbody>
</tbody>

Table pulling data into

Last Name	First Name	Unique Identifier	Course 1	Course 2	Course 3
VLOOKUP from another table	VLOOKUP from another table	VLOOKUP from another table	to include the formula	to include the formula	to include the formula

<tbody>
</tbody>

The formula I have so far is:

{=IF(C2="","",IF(OR(Data_Report[Grade]="3",Data_Report[Grade]="4"),INDEX(Data_Report[Date],MATCH(1,(Data_Report[Unique Identifier]=C2)*(Data_Report[Course]="1"),0)),"Not Applicable"))}

Basically I'd like the formula to show if there's nothing in the "unique Identifier" column then to leave the cell empty,
If there is a "unique Identifier" in cell 'C2' then to lookup the unique identifier to the data table ensuring to match the grade to either a grade 3 or 4 and additionally match the the course name and pulling through the date the course was completed.
If the grade is different to 3 or 4 then to return 'not applicable'

e.g. = Jane Doe, Course 2 should show as '17/05/2017'
John Smith, Course 3 should show as 'Not Applicable'
Jane Smith, Course 1 should show as '01/05/2015'
John Doe, Course 1 should show as a blank cell

Hoping the above makes sense and welcome any feedback and advise. I've spen forever trying to get it to work and am so close but haven't quite got it.

Many thanks in advance for reading
Wintye
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

17/05/2017

<tbody>
</tbody>

</body>

 

[TJORNERS]

Hi there, Apolgies if this is not the way to go about this but I have a variation on a similar theme that I am trying to solve. Please let me know if I should make this a separate posting.

In the following table, the full version of which is about 30k records at the moment, I am trying to sum all the sales made in the QTY column where the EVENT = "8549", and the CODE ="IB" or "CC" and if possible the CUST = "TBAT". I have a number of combos/variants on this that I will hang together once I understand the general way in which this is handled. I had thought to pivot the data first into simple total report that I then lookup into if that makes sense.

EVENT	CODE	QTY	PRICE	CUST	FEE
8549	CC	1	50.75	TBAT	0.8
8549	CC	1	50.75	TBAT	0.8
8549	IB	1	50.75	TBAT	0.8
8549	IB	1	50.75	TBAT	0.8
8582	IB	1	50.75	TBAT	0.8
8582	IB	1	50.75	TBAT	0.8
8582	AH	1	50.75	INGRESS	0.8
8549	AH	1	50.75	INGRESS	0.8
8549	CC	1	50.75	TBAT	0.8
8549	AH	1	50.75	TBAT	0.8
8549	AH	1	50.75	ENTA	0.8
8549	AH	1	50.75	ENTA	0.8
8549	AH	1	50.75	ENTA	0.8
7711	CC	1	50.75	GIGS	0.8
7711	CC	1	50.75	GIGS	0.8

<tbody>
</tbody>

 

[markmzz]

Hi there, Apolgies if this is not the way to go about this but I have a variation on a similar theme that I am trying to solve. Please let me know if I should make this a separate posting.

In the following table, the full version of which is about 30k records at the moment, I am trying to sum all the sales made in the QTY column where the EVENT = "8549", and the CODE ="IB" or "CC" and if possible the CUST = "TBAT". I have a number of combos/variants on this that I will hang together once I understand the general way in which this is handled. I had thought to pivot the data first into simple total report that I then lookup into if that makes sense.

Try this in K2:

=SUMPRODUCT(SUMIFS(C:C,A:A,H2,B:B,I2:I3,E:E,J2))

	A	B	C	D	E	F	G	H	I	J	K
1	EVENT	CODE	QTY	PRICE	CUST	FEE		EVENT	CODE	CUST	QTY
2	8549	CC	1	50.75	TBAT	0.8		8549	IB	TBAT	5
3	8549	CC	1	50.75	TBAT	0.8			CC
4	8549	IB	1	50.75	TBAT	0.8
5	8549	IB	1	50.75	TBAT	0.8
6	8582	IB	1	50.75	TBAT	0.8
7	8582	IB	1	50.75	TBAT	0.8
8	8582	AH	1	50.75	INGRESS	0.8
9	8549	AH	1	50.75	INGRESS	0.8
10	8549	CC	1	50.75	TBAT	0.8
11	8549	AH	1	50.75	TBAT	0.8
12	8549	AH	1	50.75	ENTA	0.8
13	8549	AH	1	50.75	ENTA	0.8
14	8549	AH	1	50.75	ENTA	0.8
15	7711	CC	1	50.75	GIGS	0.8
16	7711	CC	1	50.75	GIGS	0.8
17
****	********	*******	*****	*********	*********	******	**	********	********	********	*****

<tbody>
</tbody>

Markmzz

 

[TJORNERS]

Thanks Mark, i'll try that - for this to work, do the column headings have to be there in columns H,I & J or does it just use the actual data content in H2, I2:I3 and J2 for the maths to work ?

I had got somewhere similar using a DSUM function but I felt that that needed far too many criteria tables for me to then report on each event in the range I need to report on - I have 50 events in total from which I want to derive sums.

Doing it you way will greatly limit the criteria sections that I have to create which is great. Basically I can just use your basic sum and change the Event criteria field only each time for me to get what I need.

 

[TJORNERS]

Hi Mark, No need to respond to my previous submission i've worked its out and event better I can hard code one or two of the pre-reqs into the formula you gave me and then it can actually use the cell address of the event from the table in which I am going to store the results making the whole pretty much a copyable solution rather than a function that required me to edit multiple times - thanks for your steering on this - much appreciated.

 

[markmzz]

Hi Mark, No need to respond to my previous submission i've worked its out and event better I can hard code one or two of the pre-reqs into the formula you gave me and then it can actually use the cell address of the event from the table in which I am going to store the results making the whole pretty much a copyable solution rather than a function that required me to edit multiple times - thanks for your steering on this - much appreciated.

I'm glad to help and thanks for the feedback.

Markmzz

 

[edromny]

Hello I am looking to pull the closest price of a particular serial no.

The criteria being the serial no. The value returned needs to be the closest value (absolute).

Serial No.	Average
00163UAD8	105.125
00163UAD8	104.625		Criteria
00163UAD8	104.6271136		Serial No.	00163UAD8
00163UAA4	107.875		Average Bid & Ask	103.68
00163UAA4	115.065
00163UAA4	113.38		Output
00163UAA4	104.85		Average of Bid & Ask
00163UAD8	105.0255
00163UAD8	104.5953352
00163UAD8	104.9375

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Serial No.	Average
00163UAD8	105.125
00163UAD8	104.625		Criteria
00163UAD8	104.6271136		Serial No.	00163UAD8
00163UAA4	107.875		Average Bid & Ask	103.68
00163UAA4	115.065
00163UAA4	113.38		Output
00163UAA4	104.85		Average of Bid & Ask
00163UAD8	105.0255
00163UAD8	104.5953352
00163UAD8	104.9375
00163UAD8	104.8125
00163UAD8	104.1696
00163UAD8	105.755
00163UAD8	104.615
00163UAD8	104.87
00163UAA4	107.69
00163UAA4	110.1549026
00163UAA4	112.125
00163UAA4	105.2696711

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>