Match a number to the LEFT of a name across multiple arrays

[mozartk626]

I am looking for a simple formula for this, hopefully one that does not include a bunch of nested Index/matches:

See attached: I've created a sheet called "Names" where I have created a list of names in Column A. Each name is unique (no duplicate names). On my sheet named "Development," I have a formula in Column A that searches the "Names" sheet for the name in Column B of the Development sheet and return the data in the "Order #" column from the "Names" tab (or a blank cell if Order # is blank). The Count Blank Column (Column C) then counts each blank cell in Column A and assigns a number names that do NOT have a numeric value beside them in the Order # column.

I would like to expand this idea to multiple columns by doing the following:

I've created a new sheet called "Development 2." It is identical to the "Development Sheet," however, in Column A, I would like it to perform the same function as the original "Development" sheet, but I've changed the format of the "Names" Sheet. On the "Names 2" sheet, you will see that I have split the name list into multiple columns, and I have also moved the Order # column to the LEFT of each name. I would like Column A in the "Development 2" sheet to look up the name in Column B of "Development 2" and return the number to the LEFT of the name (Order #) in the "Names 2" sheet. If the Order # cell on the Names 2 sheet is blank, then the formula will return a blank cell.

Thanks.

[ORoos]

Hi mozartk626,
In the Development 2 tab, cell A2 enter: =IFERROR(INDEX('Names 2'!$A$2:$A$8,MATCH('Development 2'!B2,'Names 2'!$B$2:$B$8,0)),0)+IFERROR(INDEX('Names 2'!$D$2:$D$8,MATCH('Development 2'!B2,'Names 2'!$E$2:$E$8,0)),0)
First part is first set of names, second set second set....for the remaining 3 sets, just copy the blue part and adjust range.
If no result is found you get a 0 (zero) instead of blank. Not sure if that is a deal breaker.

[PeteABC123]

Nice job ORoos! Maybe a CF to hide the zeros.
Pete

[Bo_Ry]

Please try at A2

=IFERROR(1/(1/SUMIFS('Names 2'!$A$2:$M$9,'Names 2'!$B$2:$N$9,B2)),"")

[mozartk626]

Thanks, Bo_Ry. That worked perfectly, and it was nice and short. ORoos, your formula worked, too, but the zeros prevented the formula in Column C from working correctly. It would just need to be modified to return blank cells instead of zeros.

Thanks to all!