Find list of text in row and then return offset of matched text?

So lets say I have a list of text I want to find in particular row: Dog, Cat, Bird, Bear

I would need a macro to search the row for any of those values. When it finds one of those values, then I want it to put that value in the "Summary" tab in Cell B2. Then, I would need to offset the column where it found the match by the 2nd column to the right and put that value the "Summary" tab in Cell C2.

I have attached an example workbook showing what I would need. In "Sheet1" is where I would need to do the search by row. Then on the summary tab is where I would be entering the values returned from the matches on Sheet1.

Thanks for the help and let me know if the example workbook does not make sense.

Originally Posted by oeldere

It is not clear to me, why you want the data in the summary rapport like that.

I made a pivot table (with more values), so you can see what is possible, to analyse the data, if your data is formatted the right way.

The example is just with dummy data for ease of asking the question. Row 2 will be filled with more data than what is presented in the example. But I just need to search the row to find a match from my list of values. Then once it finds that match, find other values that will be an offset of a certain amount of columns to the right. Then, put all these values in a summary on the summary tab.

Yes, a pivot table would be easiest, however, the way the data is coming in is different for each row. So my match (bird, dog, cat, bear) could be in a different column of the row for each row. The constant is that once I find the match (bird, dog, cat, bear) the other data I need are fixed columns offset from the match. If that makes sense.

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

Works perfectly! Thank you!