Log In
Register
Home
April 25, 2022
Offline
Hi,
Need some advice. I need to create a spreadsheet in where in sheet 1 once I put the text in column A2, it will search if this name is available or present in sheet 2.
I need to use the first four characters of the name in col. A2 to find some matches in sheet 2 and it will give the results horizontally.
Example: the data that I have inputted in sheet 1 A2 is Eastman, Results will appear in B2, C2, D2 and so on.
Name(a2) Result1(b2) Result2(c3) Result3(d4)
Eastman Eastway Eastwest Eastman
Once wondering what formula to use in this.
Thanks for the help.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Provided you are on Excel for MS365 or 2021, you can use TRANSPOSE and FILTER. Let's say the list of keywords in Sheet2 is in a named range called "myList", the following formula in B2 will produce what you ask for.
=TRANSPOSE(FILTER(myList,LEFT(myList,4)=LEFT(A2,4)))
April 25, 2022
Offline
Thanks for this.
I tried using this formula but says function is not valid. Was wondering if you can attach the sample excel so I can look at it?
April 25, 2022
Offline
Hi,
it seems the filter is not working as it says function not valid. Is there a simple vlookup function instead?
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Sorry to have missed your original answer. Attaching the file I used in my example. Not that it doesn't work if you don't have Excel for MS365 or 2021.
April 25, 2022
Offline
No worries and thanks.
I tried changing the text in A2 but suddenly the formulas in B2-D2 became #Name. Even copying the formula on the next row results to that error.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I have borrowed the sample file that Riny uploaded, added two helper columns and use the first helper column to find matching data.
See attached file for an example.
Br,
Anders
