Is value lookup the best solution to my problem?

[pin0s]

Hi all,

I have been searching the forums and google for an answer to my problem and I am having trouble on firstly what to search.

I'm hoping you can either help me with a solution or get me on the right track for what to search.

My question is how can I lookup a value (being a number assigned to a week) from a drop down menu, match that to the column of the week in another table and the see if the product is on promotion that week and at what price.

Below is what Ideally I want my output to look like, when I select the week it checks (figure 2) and enters a True Value in G and the pricing data for H.

Any help would be greatly appreciated.

Figure 1:



Data: Figure 2

The weeks the product is on promotion have a numeric value in the cell and the non-promotional weeks they have a NA value.

 

[AhoyNC]

Maybe something like this using INDEX / MATCH.

Excel Workbook
	C	D	E	F	G	H
3	Week	4		Product	On Promotion	Price
4				Product1	Yes	8.99
5				Product2	Yes	7
6				Product3	No	NA
7				Product4	No	NA
8				Product5	Yes	5.25
Sheet1

Excel Workbook
	C	D	E	F	G	H
5	Product/Week	1	2	3	4	5
6	Product1	NA	NA	NA	8.99	NA
7	Product2	NA	NA	6.99	7.00	NA
8	Product3	NA	NA	NA	NA	NA
9	Product4	NA	5.98	NA	NA	NA
10	Product5	NA	NA	NA	5.25
11	Product6	NA	NA	NA	NA	NA
Sheet2

 

[pin0s]

Thank you very much that solved the problem and now I know to use the MATCH method!

Really appreciate you taking the time to help me, I can't thank you enough!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.