Index match function

[Chris945]

Dear...,
I'm having some problem making the index match function to work properly. It does work for a few times, and then I get the message
"unable to ge the index function of the worksheetfunction class.
A B C D E F...

Unique product nr	Project manager	Item number	Quantity	Item description	Capacity/hour	Project number	Assigned to	Assigned status
1​	Tom	01.20.000	1​	item1(01 of 02)	60.000	24.000	Pending	Pending
3​	Tom	01.20.000	1​	item1 (02 of 02)	60.000	24.000	Pending	Pending
2​	Boris	01.20.000	1​	item3 (01 of 02)	60.000	24.003	Thomas	Approved
4​	Boris	01.20.000	1​	item3 (01 of 02)	60.000	24.003	Pending	Pending

Each product gets a unique nr when starting as seen in col A. A customer number and name and an item are linked with this unique number.
A customer can be several times in the list, as seen in colum G, but an item description can only be once per customer.
In other words there should be only one match if the index mach function with 2 critera is used. example: criteria1; item description = item1(02 of 02) critera2; Project number= 24.000 Index on the first colum should give me 3 as a result.
Although the data in the above table is fictive, it represents the idea. My index match function would look as follows:
indexMatch1= Application.worksheetfunction.Index.(Sheets("this sheet").Range("A2:A"&lastrow"),Application.worksheetfunction.Match("item1(02 of 02)",Sheets("this sheet").Range("E2:E"&lastrow),0),Application.worksheetfunction.Match("24.000",Sheets("this sheet").Range("G2:G"&lastrow),0))
I've been 'playing with the ranges like for the index range ("A2:Q"&lastrow), or for the second match a range("A1:Q1) as suggested on another forum. I just can't figure it out how to use the function properly.
I'm open for all suggestions, even if index match would not be involved in the solution, every suggestion to overcome my problem is welcome. Thanking you in advance.

 

[Alex Blakenburg]

Step 1 is to get your formula to work in Excel before trying to figure it out in VBA.
I don't believe your formula actually works.
Also your formula currently uses "24.000" which indicates that Project no is text and that it is formatted as #.000 please confirm if that is the case.

Try something like the below in Excel and if it does what you want then we can convert it to VBA.

=XLOOKUP(1,($E$2:$E$10="item1 (02 of 02)")*(TEXT($G$2:$G$10,"#.000")="24.000"),$A$2:$A$10,"")

 

[Chris945]

Step 1 is to get your formula to work in Excel before trying to figure it out in VBA.
I don't believe your formula actually works.
Also your formula currently uses "24.000" which indicates that Project no is text and that it is formatted as #.000 please confirm if that is the case.

Try something like the below in Excel and if it does what you want then we can convert it to VBA.

=XLOOKUP(1,($E$2:$E$10="item1 (02 of 02)")*(TEXT($G$2:$G$10,"#.000")="24.000"),$A$2:$A$10,"")

Dear Alex,
This is indeed the case, the project number is text.
Meanwhile, I have resolved the issue using Find, check if the condition is fullfilled, if not next find else my code...
Thank you for your assistance and pointing out the problem with text/numeric.
I will mark your reply as solved if I find how to do so.
Regards

 

[Alex Blakenburg]

Thanks for the update glad it helped. If you want to post the code you ended up using and worked for you then you can mark your own post with the solution as the solution.