index match with variables

[markchan74]

hi all,
I been playing with index-match but I couldnt get it to work on variable inputs. Hope someone can guide me on how I can do the following,

Input User and Fruit and it will return the date where the user has this fruit.

Capture.PNG

Thank you

[protonLeah]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[markchan74]

sample.xls

I have attached the sample file. Not sure if it works.

[samba_ravi]

Example Henry Banana 29-Sep
your example shows as above
i think it is 30-Sep am i right?

[samba_ravi]

If i am right try this in Cell E14
=INDEX($C$3:$G$7,MATCH("Date",$C$3:$C$7,0),MATCH($D14,OFFSET($C$3,MATCH($C14,$C$3:$C$7,0)-1,0,1,COLUMNS($C$3:$G$7)),0))

[boopathiraja]

HI, Refer the attachment with formulas

[Punnam]

Please Login or Register  to view this content.

With Index + Indirect Just for knowledge

Punnam

[markchan74]

Example Henry Banana 29-Sep
your example shows as above
i think it is 30-Sep am i right?

Yes, my bad, it is 30 Sep

[markchan74]

Thank you all

[samba_ravi]

Thanks for your feedback and reputation points

[markchan74]

trial.xls

Hi guys,
What if i want to find something different? See attached.
I need to find the info highlighted from the table above.

Thanks

[markchan74]

Attachment 357252

Hi guys,
What if i want to find something different? See attached.
I need to find the info highlighted from the table above.

Thanks

[twiggywales]

hi mark,

if you are looking for information on INDEX i have a link in my sig that shows how it works and some of its less know uses


http://www.excelforum.com/tips-and-t...ml#post3882837

[twiggywales]

in your example you have a row where cat is called off twice

see my example sheet.

trial.xls

how would you want to deal with that?

[markchan74]

Hi Twiggy,
Thanks for the link to index. I will read it up.
Thanks for the solution too.

Yes, what if I have such a scenario? I can see that, it can only pick the first one it seen. Is it possible to have a "loop" to put more than one answer in a cell, perhaps maybe using "&" ?