Formula needed to return data over a series of columns

i have a report that has branch information somewhere across columns D2:I2 (the branch info was not allocated to a particular column) - what i need is a formula that will look across the columns to find the name of the "branch:**" then return information about that branch located on another sheet.

i have tried IF and VLOOKUPS and i can't get anything to work. i have looked at MATCH

IF branch:London then return branch:London,region=Homeworking,area=Homeworking

i have made sure that the return data is in one column - but wonder if the trouble i am having is because the branch data could be anywhere in the columns.

i have over 300 branches and don't want to add their names to the forumla and over 4000 lines of data to match.

Hi, Dixie, welcome to Excel Forum! A small sample workbook (NOT a picture) would really help us to solve your problem quickly and accurately.
Please remove any sensitive or extraneous info.
Try to preserve the original layout so our solutions fit your workbook.
Provide “realistic” data. Include any variations the code or formula must address.
If appropriate, perhaps simulate some results to demonstrate what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now click Browse, find your file, then click Upload. Simple!

This assumes that you are looking for the first cell with a value in one of the columns D:I

Try this to get your lookup value to feed into the lookup formula

Formula:

Please Login or Register  to view this content.

Here is formula with lookup aimed at another sheet in same workbook

Formula:

Please Login or Register  to view this content.

And modified to lookup another workbook

Formula:

Please Login or Register  to view this content.

The link to another file will not work in the attached, and columnL formula will require modifying.

This, if you need to find the cell containing "branch*":

Please Login or Register  to view this content.

1. there are extraneous spaces before all of the text in cols D-I. A bit of a pain, but this formula deals with that.

2. Portsmouth is spelled incorrectly in sheet2.

3. Your expected answer for London was incorrect.

=IFERROR(INDEX(Sheet2!$A$2:$A$4,MATCH("*"&INDEX(Sheet2!$A$2:$A$4,MATCH("*"&TRIM(INDEX(A2:H2,MATCH("*branch:*",A2:H2,0)))&"*",Sheet2!$A$2:$A$4,0))&"*",Sheet2!$A$2:$A$4,0)),"")

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.