Formula to identify cell contents

It would be easier to help if you would upload a file instead of a picture. To upload a sample workbook click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
With that said, looking at the formula and reading the narrative, especially the part "if A2 was a match I would like B2 to be printed", try the following:

Formula:

=IFERROR(IF(MATCH($C2,HERB_1!$A:$A,0),INDEX(HERB_1!$B:$B,MATCH($C2,HERB_1!$A:$A,0)),),"")

I took out the first 'MATCH' in the formula (post #1), as it would always be true that C2 will be a part of set C:C. The formula also seems to be looking for matches in column B of HERB_1, instead of column A as described in the narrative. If that is the case then there will need to be changes in the Lookup_Array arguments of the MATCH functions as well as the Array argument of the INDEX function.
Let me know if you have any questions.

This solution is as per location test T7 where the first collector is given (although two are listed on the Herb_1 sheet), and will only partially help as per T10.
Since Herb_1 lists the species in column B and collector in column C paste the following formula in K2 and double click the fill handle:

Formula:

=IFERROR(IF(MATCH($C2,HERB_1!$B:$B,0),INDEX(HERB_1!C:C,MATCH($C2,HERB_1!B:B,0),),""),0)

Since Herb_2 through Herb_5 list the species in column A and collector in column B, paste the following formula in L2, copy across to O2 then double click the fill handle to copy down:

Formula:

=IFERROR(IF(MATCH($C2,INDIRECT(L$1&"!$A:$A"),0),INDEX(INDIRECT(L$1&"!B:B"),MATCH($C2,INDIRECT(L$1&"!$A:$A"),0)),""),0)

Let me know if you have any questions.

Played around with this a little more and have an updated solution for collectors that have multiple citations.
Note: Helper columns in AA:AO
Let me know if you have any questions.

An easy fix would be to display the entire citation in each case such as, for Herb_1 (Viburnum acerifolium Linnaeus): Franks 194,Franks 357,Franks 523 Take a look at the column (AQ) headed Alt Herb_1 as an example of how this would look.
Let me know if you have any questions.

Oh that is an excellent fix! I do have one question about the helper columns. Is herb_1 repeated from AA to AC because there were three occurrences of that citation? I guess my question is I have some plants that have upward of 12 citations would I need to make 12 herb_1 helper columns to allow for the maximum possible citations? Thank you again and best regards!

Yes there are three helper columns because I was under the impression that there would be a maximum of three citations. If there is a possibility of 12 citations then you would need 12 helper columns to be sure that you got all of them, thankfully excel provides 16,000 + columns.

Looking at the file the formula in AA2 was entered as a regular formula, and looking back I never mentioned we were working with array entered formulas*, sorry about that.
*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

So select AA2, press the F2 key (which puts the cell back in edit mode), then press Ctrl, Shift and Enter simultaneously. You should then be able to copy the formula over to AP2 and down as far as needed.
Let me know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.