This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am having difficulty with creating two complex (to me) formulas.
I have 2 worksheets. I'm working with.
1st Formula
Worksheet 1 Column A has a list of locations. I am wanting a formula to find that "word" in Worksheet 2 Column B and return text beside it that is in Worksheet 2 Column A.
Worksheet 1 Columns A & B
| Branch | Billing Customer |
|---|---|
| Richmond | 00424 |
| Columbia | 00481 |
| North GA | 00481 |
| Plainville | 00827 |
| N Boston | 00850 |
Worksheet 2 Columns A-D
| Code | Branch | Account # | ID |
|---|---|---|---|
| E4891 | PROPERTY OF RICHMOND | 00424 | 00424 |
| Q9980 | THE PROPERTY OF - PEABODY | 00850 | 00850 |
| Q2823 | THE PROPERTY OF NEW ENGLAND | Q2823-0122 | 1588 |
| Q6837 | THE PROPERTY OF - CHESAPEAKE | Q6837-0120 | 03982 |
| P4369 | THE PROPERTY OF-COLUMBIA | P4369-0126 | 03982 |
| Q9980 | PEABODY | Q9980-0002 | N BOSTON |
2nd Formula: - The most complex if at all possible and most needed.
I am wanting a formula that looks at Worksheet 1 Column B (see above), finds the exact match in Worksheet 2 Column D and returns the value in Worksheet 2 Column C, BUT, and this is where it becomes complex, because there can be duplicates in Worksheet 2 Column D then Worksheet 2 Column A must match the results from the 1st formula, if not, to look for that value in Worksheet 2 Column A and then return the appropriate text from Worksheet 2 Column C.
Worksheet 2 Columns A-D
| Code | Branch | Account # | ID |
|---|---|---|---|
| E4891 | PROPERTY OF RICHMOND | 00424 | 00424 |
| Q9980 | THE PROPERTY OF - PEABODY | 00850 | 00850 |
| Q6837 | THE PROPERTY OF - CHESAPEAKE | Q6837-0120 | 03982 |
| P4369 | THE PROPERTY OF-COLUMBIA | P4369-0126 | 03982 |
| Q9980 | PEABODY | Q9980-0002 | N BOSTON |
| Q2755 | THE PROPERTY OF-CLEWISTON | Q2755-0091 | 01239 |
|---|---|---|---|
| Q5387 | THE PROPERTY OF-MIAMI | Q5387-0097 | 01239 |
| R221 | THE PROPERTY OF-FT.LAUDERDALE | R221-0094 | 01239 |
| E4891 | PROPERTY OF RICHMOND | 01478 | 01478 |
| 3748B | PROPERTY OF -SOUTHERN CT | 3748B-0035 | 1588 |
| P102 | RHODE ISL PROPERTY | P102-0114 | 1588 |
| Q2823 | THE PROPERTY OF NEW ENGLAND | Q2823-0122 | 1588 |
| Q5098 | THE PROPERTY OF-FITCHBURG | Q5098-0094 | 1588 |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
I cannot understand the first question. For the first one, enter this formula in cell C2 of table1
=XLOOKUP("*"&A2&"*",$H$2:$H$7,$G$2:$G$7,"",2)
Hope this helps.
Hi,
In cell C2, enter this formula
=XLOOKUP(1,(($H$2:$H$15=B2)*(ISNUMBER(SEARCH(A2,$F$2:$F$15)))),$G$2:$G$15,"")
Hope this helps.
Try this one. If it doesn't you want show us the expected result.
=FILTER(Sheet2!C:C,(Sheet2!D:D=H2)*ISNUMBER(SEARCH(G2,Sheet2!B:B)))
That is perfect for the 1st formula I needed. This will be much faster than the find & replace option I was doing. Is it possible for the formula to look at different worksheet in the same Spreadsheet? If not I can work around that.
I'll try to explain my 2nd formula request a bit better.
In Worksheet 2, Column D, there are several ID's that are the same but they have different Account #'s (C) based on the Branch (B) and it's corresponding Code (A). I would like for the formula to find the correct Account # based on the correct Code (A)