Formula to identify cell contents

**MarineBotanist** · 09-11-2016, 03:40 PM

Hello I am new here and I am in need of some help. I have a workbook with columns K, L, M, N and O that represent sheets in the workbook. What I want to do is check each sheet to see if there is a match to each species in column C like I have with the Xs in the image. However, what I would like to be able to do is instead of an X I would like excel to reference the contents of the neighbor of matched cell from the other sheets (ie if A2 was a match I would like B2 to be printed instead of an X; also ideally if possible if A2, A5, ..., An were a match have a printout that would be the values of B2, B5, ..., Bn all in place of the x). Would any of you know if this is possible? Currently the formula I am using is as follows: =IFERROR(IF(AND(MATCH($C2,$C:$C,0),MATCH($C2,HERB_1!$B:$B,0)),"x",),"")

I greatly appreciate any help with this!

**JeteMc** · 09-11-2016, 06:16 PM

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:

Please Login or Register  to view this content.

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.

**MarineBotanist** · 09-11-2016, 07:01 PM

Okay that is understandable. Here is a sample of the workbook that I condensed and added a note to. Again I appreciate your help! I am currently reading and trying to edit the workbook based off your first comment JeteMC.

**JeteMc** · 09-11-2016, 09:11 PM

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Let me know if you have any questions.

**JeteMc** · 09-11-2016, 10:42 PM

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.

**MarineBotanist** · 09-14-2016, 08:56 PM

I thought I had replied to this a while back apparently it did not submit. I played around with your code some more however, I must say that it is beyond my currently understanding of excel formulas. I noticed some things one was if there were multiple collector citations only the first collector would be listed with all the other collectors numbers. The other thing I noticed was that in some cases if there were multiple collector numbers the other numbers had numbers missing from them (L28 and O19). I am attempting my best to figure out a solution if it may be easier to have each collector and the number listed i.e. Sawyer 2341, 1342; Lin 235 etc. I really appreciate your help with the coding and thank you so much again it is close to exactly what I was hoping for!

**JeteMc** · 09-15-2016, 08:53 AM

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.

**MarineBotanist** · 09-15-2016, 09:08 AM

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!

**JeteMc** · 09-15-2016, 09:21 AM

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.

**MarineBotanist** · 09-15-2016, 11:21 AM

Okay so I attempted to make each Herb_x have 15 columns and I think I messed something up. I replaced the Herb_x in columns A to O with the Alt Herb_x and the one last thing I was trying was to give each of them a searching range of 1000 cells instead of the 40 I have in the sample. When I modified the cell AA2 it worked however, when I modified the cells AB2 it did not. All I did was change the 40 in "=IFERROR(IF(MATCH($C2,INDIRECT(AB$1&"!$b2:$b1000"),0),INDEX(INDIRECT(AB$1&"!c2:c1000"),SMALL(IFERROR(ROW(B$1:B$1000)/(INDIRECT(AB$1&"!$b2:$b1000")=$C2),FALSE),COUNTIFS($AA$1:AB$1,AB$1)))),"")" to 1000. I am not sure if that was the right thing to do or not. I am pretty sure this would be the last thing I need to fix. What am I doing wrong with changing the 40s to 1000s?

**JeteMc** · 09-15-2016, 02:27 PM

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.

**MarineBotanist** · 09-19-2016, 05:09 PM

JeteMc I want to say thank you so much for your help! After I applied your formulas and help everything worked really well. I really really

appreciate all your help!

**JeteMc** · 09-22-2016, 06:52 PM

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.

Formula to identify cell contents

LinkBack

Thread Tools

Rate This Thread

Display

Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Re: Formula to identify cell contents

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Convert contents of cell created by a formula back to its contents.

[SOLVED] help identify the string contents time

[VBA] Identify cell rows based on criteria and sum contents of a corresponding cells

Identify amount of related cells, Merge contents accordingly, move contents

Formula to identify a title and return the contents of the group

Cell Contents to identify source Filename

[SOLVED] In Excel, how do I identify the contents of a cell as numerical o.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions