Matching Data (Index Match wont work)

[sibehkeng]

I have two sets of data which i needed to match price of a product with its raw data

1st Set of Data (The worksheet which i needed match against the master price list)
Date Amount
10/11/2016 265.00
14/11/2016 632.00
15/11/2016 184.00
19/11/2016 2,080.00
20/11/2016 644.00
21/11/2016 632.00

2nd Set of Data (The master price list)
Notes:
*1 - 18 indicates the different pricing that the product might have
*the price might stay stagnant or fluctuates in a few days time (For this example it fluctuates twice)
*Actual data covers one year of fluctuation and thousands of data from worksheet to be matched against

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
10/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
11/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
12/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
13/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
14/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
15/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
16/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
17/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
18/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
19/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
20/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
21/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
22/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
23/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
24/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
25/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
26/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
27/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
28/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
29/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
30/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304

<tbody>
</tbody>

Our Objective is to ensure the selling price (our worksheet) can be matched against the date in the raw data, with the selling price match any of the 18 prices listed in the price list. If it matches, then we shall either retrieve the figure, or just retrieve "Yes".

Been struggling for this for awhile, HELP! ray:

 

[marshy3300]

=if(index(master!$b$2:$p$31,match($a2,master!$a$2:$a$31,0))=$b2,"Yes","")

 

[sibehkeng]

Thanks! Couldn't work though...

 

[marshy3300]

try
=IF(INDEX(Master!$B$1:$S$1,MATCH(B6,INDEX(Master!$B$2:$S$22,MATCH(A6,Master$A$2:$A$22,0),),0))>0,"Yes","Price not Found")

Expand to suit

 

[Peter_SSs]

Welcome to the MrExcel board!

I have done this on one sheet, but if it is what you want, it could be adapted for two.

If it isn't what you require, don't just say "couldn't work" or "doesn't work", give specific examples of incorrect results and what the correct result should be and why.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Date	Amount	Yes/No			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	10/11/2016	265.00	Yes		10/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
3	14/11/2016	632.00	No		11/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
4	15/11/2016	184.00	Yes		12/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
5	19/11/2016	2,080.00	No		13/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
6	20/11/2016	644.00	No		14/11/2016	236	236	217	212	177	155	354	354	325	318	265	232	467	467	429	420	350	310
7	21/11/2016	632.00	No		15/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
8					16/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
9					17/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
10					18/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
11					19/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
12					20/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
13					21/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
14					22/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
15					23/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
16					24/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
17					25/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
18					26/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
19					27/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
20					28/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
21					29/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
22					30/11/2016	233	233	214	209	184	152	348	348	320	313	261	228	459	459	422	413	344	304
Check Price Exists

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(INDEX(F$2:W$22,MATCH(A2,E$2:E$22,0),0),B2),"Yes","No")