3 Way Input

[origano]

My excel file can be downloaded here.

In my spreadsheet, the user can have a maximum of 3 inputs that is:

1. Age
2. Day
3. Pickup Toys

And based on the inputs, it should be able to do a strict (or exact as we call it) match from the data given within. Meaning if:

Age AND Day matches BUT Pickup Toys doesn't then return nothing.


In addition to this, the user can also have 2 inputs i.e.:

1. Age AND Pickup Toys (while Day is left blank)
2. or, Age AND Day (while Pickup Toys is left blank)
3. or, Pickup Toys AND Day (while Age is left blank)

In case of 2 inputs say Age AND Pickup Toys are entered, we see that Day is left blank. So the search formula (In Prize Status cell to retrieve data about it) should match it exactly.


Meaning that if Age AND Pickup Toys matches BUT Day in the table is something else other than blank (i.e., in the table it contain some values unlike the input) - such a match should be strictly rejected.


I have tried doing it with MATCH, INDEX, and SUMPRODUCT functions but the dilemma is sometimes it's working and sometimes not.

	A	B	C	D	E	F
1	INPUT DATA INTO THE YELLOW FIELDS					THESE 2 CELLS BELOW SHOULD AUTOMATICALLY BE CALCULATED & RETRIEVED FROM THE TABLE BELOW
2		Age			Prize Status
3		Day	Sunday		What Others are saying
4		Pickup Toys	Thumbtack
5
6
7	Age of Winner	Day	Pickup Items	Prize Status	What Others are saying
8	14	Friday	Gun	Available	Cool
9	32	Saturday	Ball	Out of Stock	Nice
10		Wednesday	Plane	Out of Stock	You've won a Jackpot!
11	18	Sunday	Top	Available	Nice
12	21		PS3	Available	You've won a Jackpot!
13		Sunday	Laptop	Available	Woohoo!
14		Friday	Bike	Out of Stock	You've won a Jackpot!
15	63	Thursday	PS3	Available	Cool
16	27	Saturday	Thumbtack	Out of Stock	Nice
17	72	Monday	Ipad	Out of Stock	Woohoo!
18	14	Wednesday	Juice Extractor	Available

<tbody>
</tbody>

Thanks,
Jerry

 

[RatExcel]

=INDEX(D8:D18,MATCH(C2&C3&C4,A8:A18&B8:B18&C8:C18,0))

When entering this formula use CTRL+SHIFT+ENTER not just ENTER

Your sheet: https://app.box.com/s/fbay39sbuwv5avno7ut9

 

[origano]

Thanks @RatExcel The "&" was a great idea.

Now I have another question though. Assume in 1 row of the table data, it says:

	A	B	C	D	E
7	Age of Winner	Day	Pickup Items	Prize Status	What Others are saying
8		Tuesday	Bike	Available	Woohoo!

<tbody>
</tbody>

And in the yellow cells, if I input:

	B	C
2	Age	18
3	Day	Tuesday
4	Pickup Toys	Bike

<tbody>
</tbody>

or if I input:


<table class="wysiwyg_dashes" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" font-size:="" 13.1428575515747px;"="" width=""><tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]Age[/TD]
[TD="bgcolor: #FFFFCC, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]Day[/TD]
[TD="bgcolor: #FFFFCC, align: right"]Tuesday[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]Pickup Toys[/TD]
[TD="bgcolor: #FFFFCC, align: right"]Bike

[/TD]
[/TR]
</tbody></table>

In the table data, we see Age of Winner value is given to be an EMPTY cell meaning anybody can participate to win the prize because there is no restriction in age.

So If I were to input Age of Winner as 18, 25 or 37 or whatever number (for when a cell is blank) or even if I leave the Age input cell blank, and while there I input:
Day=Tuesday and Pickup Items = BIke; it should return me the Prize Status = Available.

Should we use something like XOR (or maybe NOT?) function to do that query? The same goes in for any other cell when is blanked in the table data, i.e., say in the table data Day is blank. So the input day can be ANY (such as Tuesday, Thursday, Friday etc.) but it should return me the Prize Status, and What others are saying when a match is found.

Jerry

 

[RatExcel]

=INDEX(D8:D18,IF(C2="",MATCH(C3&C4,B8:B18&C8:C18,0),IF(C3="",MATCH(C2&C4,A8:A18&C8:C18,0),IF(C4="",MATCH(C2&C3,A8:A18&B8:B18,0),MATCH(C2&C3&C4,A8:A18&B8:B18&C8:C18,0)))))

CTRL + SHIFT + ENTER needed!

Here you go: https://app.box.com/s/8nqbthre5r5cqy8miu75

 

[origano]

=INDEX(D8:D18,IF(C2="",MATCH(C3&C4,B8:B18&C8:C18,0),IF(C3="",MATCH(C2&C4,A8:A18&C8:C18,0),IF(C4="",MATCH(C2&C3,A8:A18&B8:B18,0),MATCH(C2&C3&C4,A8:A18&B8:B18&C8:C18,0)))))

CTRL + SHIFT + ENTER needed!

Here you go: https://app.box.com/s/8nqbthre5r5cqy8miu75

Thanks but this is not what I meant. Here's what I meant:

	A	B	C	D	E	F
1	INPUT DATA INTO THE YELLOW FIELDS				THESE 2 CELLS BELOW SHOULD AUTOMATICALLY BE CALCULATED & RETRIVED FROM THE TABLE BELOW
2		Age			Prize Status	Available
3		Day	Sunday		What Others are saying	Woohoo!
4		Pickup Toys	Laptop
5
6
7	Age of Winner	Day	Pickup Items	Prize Status	What Others are saying
8	14	Friday	Gun	Available	Cool
9	32	Saturday	Ball	Out of Stock	Nice
10		Wednesday	Plane	Out of Stock	You've won a Jackpot!
11	18	Sunday	Top	Available	Nice
12	21		PS3	Available	You've won a Jackpot!
13		Sunday	Laptop	Available	Woohoo!
14		Friday	Bike	Out of Stock	You've won a Jackpot!
15	63	Thursday	PS3	Available	Cool
16	27	Saturday	Thumbtack	Out of Stock	Nice
17	72	Monday	Ipad	Out of Stock	Woohoo!
18	14	Wednesday	Juice Extractor	Available

<tbody>
</tbody>

As you can see in the table above, the Age input value is left to blank; and Day = Sunday and Pickup Toys = Laptop. A match is found.

But here's the thing what I want it to be able to do:
If in the table data, when queried, if a match is found where the input Day (Sunday) & Pickup Toys (Laptop) AND the Age of Winner data for that particular row is given as blank - it means should we enter Age as either blank or any numerical value, this match should be returned.

Long story put short: Irrespective of the Age value, whether it's left to blank as input or whatever value - because in the table data below it gives the Age of Winner as BLANK, to mean match should be found regardless of age value input, i.e. Whether you enter Age as 18 or whatever number or even left it blank, it should return you the same as in the table above.

 

[RatExcel]

https://app.box.com/s/uc5y3cgvdjma0pld1sap