LOOKUP within a IF statement

Originally Posted by ChemistB

It looks like you want it to look up and return multiple values, not "a" value. Is that true?

Yes, as shown in the example. The TEST1 part number should have 1 under Lathe1, 2 under Lathe2, 3 under Lathe3, and 4 under Lathe5 in the Matrix tab.

Originally Posted by NBVC

Use SUMIFS like you did the COUNTIFS just adding the sum range....

In J4:

=IF(COUNTIFS(Routing!$B:$B,$B4,Routing!$D:$D,J$2),SUMIFS(Routing!$F:$F,Routing!$B:$B,$B4, Routing!$D:$D,J$2),"")

I need the specific value in column F in the routing tab.
For Example, cell AS with the sumif it is adding the 3 +7 to get 10, anyway to get "3,7" ????

Originally Posted by NBVC

I think that is what my formula does for you....

IF the part is routed to the same machine more than once the formula is adding it together.
I need the specific value in column F in the routing tab.
For Example, cell AS with the sumif it is adding the 3 +7 to get 10, anyway to get "3,7" ????

Originally Posted by NBVC

Try adding this UDF to your VB Editor:

Please Login or Register  to view this content.

Then in J4 use formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER.


P.s. I sure hope Microsoft improves the CONCAT function to allow for ranges... this would be much more useful...

OK, your way over my head, how do you "Try adding this UDF to your VB Editor:"

Originally Posted by scottylad2

have a look and see if this is what you're looking for

Yes, it works and it returns the 1st value, the problem is sometimes the part is routed back to the same machine (see part N123) Seq Nbr 3 and Seq 7 are both CNC Grind4

Originally Posted by NBVC

While in the worksheet, hit ALT+F11, go to Insert and select Module, paste the first code.

Then in the worksheet, in J4, enter the formula and confirm with CTRL+SHIFT+ENTER and then copy across and down

I'm using Microsoft 2007, I do not see "Module"

Originally Posted by NBVC

When you get to the VB Editor by hitting ALT+F11, there should be and Insert menu at the top... click and select Module from that.. the editor will be clear and ready for you to paste the macro in....

I attached your workbook with the macro and formula in place.

I must of had a fat finger, I tried it again and it worked

Originally Posted by NBVC

While in the worksheet, hit ALT+F11, go to Insert and select Module, paste the first code.

Then in the worksheet, in J4, enter the formula and confirm with CTRL+SHIFT+ENTER and then copy across and down

It works great!! Thanks the help. I really need to take a class on this stuff. It is amazing what you can do.

Thanks again for your time.