Mulitple Criteria Index,Match or Evaluate issue

[Journeyman3000]

Hello Excel Forum Community.

I am a new forum member, have worked for two days on this problem, and have miserably failed at every step.

I have also tried to get an answer from another forum (OzGrid), and while helpful, i was still not able to get it to work. http://www.ozgrid.com/forum/showthread.php?t=175849

The issue:

I have two worksheets. The first contains two combo boxes from which I select dependant data (works great), and the second contains a database of multiple columns: Name, Type, Value. E.g:

Apple, iPhone, Good
Apple, Juice, Better
Apple, Pie, Best
Orange, Fruit, 2
Orange, Pens, 5
Orange, Paint, 3

The Code I have so far:

ReturnedValue = Evaluate("=Index('MyDatabase'!$E$3:$E$128,Match(" & CmbName.Value & CmbType.Value &", & 'MyDatabase'!$B$3:$B$128 ), 0), 1)")

Also

ReturnedValue = Application.Index("'MyDatabase'!$E$3:$E$128", Application.Match(CmbName.Value, Range("'MyDatabase'!$A$3:$A$128", 0), Application.Match(CmbType.Value, Range("'MyDatabase'!$B$3:$B$128", 0))

In principle I should select two value from two comboboxs and get a result.

I have tried all kinds of perutations of either of the above code lines and get a variety of errors, such as 2015 or type mismatch errors. e.g:

Sheets("MyDatabase").Range("$A$3:$A$128")



What should happen
<imagine any kind of explitive curse commonly associated with things that should work but don't>

I should be able to select two items from the combo boxes and get a corresponding value. sounds simple. It's not.


I wonder if anybody with an understanding on VBA help on this. I do not want to use a formula in a cell to get the data as the function will be run in a new sheet.

Cheers

jrnymn

[Journeyman3000]

70 views and nobody knows anything about mulitple criteria index matching in vba? One would have thought this to be valuable information to know.

Further to the above post, the following code functions return a 2015 error. Playing around with these seems to result in a type mismatch error also. Strings and ranges are declared.

Please Login or Register  to view this content.

I really need help to make this work.

Cheers