+ Reply to Thread
Results 1 to 21 of 21

match 1 cell to column then spit out a different cell

  1. #1
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    match 1 cell to column then spit out a different cell

    Hello I have a bunch of phone numbers. I send them off to get verified then once the software sends it back the numbers are all scrambled up so I have to manually search each one to find the address. I want to search one cell to find a match in 3 columns then spit out the address. Basically search BG3 in columns ab2:af1000 then return with cell D2. Thank you

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: match 1 cell to column then spit out a different cell

    Welcome to the forum!

    Scramble and spit out - sounds delightful ...

    I'd love to help, but will need a sample workbook, please.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    OK I uploaded the attachment and not sure what to do next. Can you view it?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    Ok I see it uploaded. I want it to search BG2 phone number through AB2:AF1000 for a match then return the address in column D on that same line to cell BH2. Im sorry I hope that makes sense and thank you for the help

  5. #5
    Forum Contributor
    Join Date
    04-19-2015
    Location
    pakistan
    MS-Off Ver
    2013
    Posts
    335

    Re: match 1 cell to column then spit out a different cell

    put below formula in BH2 & link D2 with with BH2, review attached..

    =IF(COUNTIF($AB$2:$AQ$2,BG2),$X$2,IF(COUNTIF($AB$3:$AQ$3,BG2),$X$3,IF(COUNTIF($AB$4:$AQ$4,BG2),$X$4,IF(COUNTIF($AB$5:$AQ$5,BG2),$X$5,""))))
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: match 1 cell to column then spit out a different cell

    gondal, that is a slick way of doing that. However, the OP has indicated that have at least 1000 lines of info. Extending that formula would be impractical, if even possible
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: match 1 cell to column then spit out a different cell

    wisdomracer, welcome to the forum

    Aside from some small discrepancies - which I assumed to be typos (AB5=xxx885, you wanted xxx8851, BH4=503xxx, column says 5503xxx), try this in BH3,..
    =INDEX($D$2:$D$5,MATCH("*"&BG3&"*",$AB$2:$AB$5&$AC$2:$AC$5&$AD$2:$AD$5&$AE$2:$AE$5&$AF$2:$AF$5&$AG$2:$AG$5,0))
    ...confirmed 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.

    Then copy down as needed.
    Also, adjust ranges as needed

  8. #8
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    I tried this and I couldnt get it to work but ya also I will have around 1000 lines. I made a sample google sheet I was going to share but I am to new to this site and it wont let me put links.

    //docs.google.com/spreadsheets/d/1nAZp1SpBLMVbPMQP58Wm1JJU7U96IlZDKOwKwARGexE/edit?usp=sharing[/url]

  9. #9
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    Ok got that. So it is https: before the link above

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: match 1 cell to column then spit out a different cell

    Here is your file attached with my suggested formula in col BJ. I suspect that you did not enter it using CTRL SHIFT ENTER?
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    Ok FDibbins I tried your formula and it worked on my sample sheet but then I tried on the larger sheet and it didnt work. This is only searching lines 2-5 correct? How would I easily expand this to do 1000 lines?
    Last edited by wisdomracer; 10-16-2018 at 04:14 PM.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: match 1 cell to column then spit out a different cell

    Yes, those rows that you provided

    You would need to change the ranges in the BOLDED part...
    =INDEX($D$2:$D$5,MATCH("*"&BG3&"*",$AB$2:$AB$5&$AC$2:$AC$5&$AD$2:$AD$5&$AE$2:$AE$5&$AF$2:$AF$5&$AG$2:$AG$5,0))

    Probably the simplest way would be to just change the "5" to "1000", then repeat the copy process

  13. #13
    Forum Contributor
    Join Date
    04-19-2015
    Location
    pakistan
    MS-Off Ver
    2013
    Posts
    335

    Re: match 1 cell to column then spit out a different cell

    Quote Originally Posted by FDibbins View Post
    at least 1000 lines of info. Extending that formula would be impractical, if even possible
    opps sorry may i misread it, although this not possible for 1k line, how it will be impractical for big lines??

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: match 1 cell to column then spit out a different cell

    What I meant was, I dont think you could even next that many IF's (to test 1000 rows) in 1 formula

  15. #15
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    Ok. Ya I feel like there has to be a formula to do this but I cannot figure it out

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: match 1 cell to column then spit out a different cell

    The formula I gave you, works in excel. I think Docs handles array formulas a but differently

  17. #17
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: match 1 cell to column then spit out a different cell

    Maybe,

    In BH3, copied down :

    =INDEX(D$2:D$5,SUMPRODUCT((ROW(AB$2:AB$5)-ROW(AB$2)+1)*(AB$2:AG$5=BG3)))

    Regards
    Bosco

  18. #18
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    Same problem. This works in the first 5 rows but I need this to work with 1000 rows

  19. #19
    Registered User
    Join Date
    10-16-2018
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    9

    Re: match 1 cell to column then spit out a different cell

    Ok I made a new sample excel file of exactly what I need. I need it to match the phone numbers in the BG with phone numbers in AB-AG and return with the address in column X of the same line it matched the numbers with.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: match 1 cell to column then spit out a different cell

    Perhaps……………

    In BH2, copied down :

    =INDEX(D$1:D$199,SUMPRODUCT(ROW(AB$1:AB$199)*(AB$1:AQ$199=BG2)))

    Regards
    Bosco

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: match 1 cell to column then spit out a different cell

    Here is my same formula, updated with the extra rows and some added columns...
    =INDEX($X$1:$X$1000,MATCH("*"&BG1&"*",$AB$1:$AB$1000&$AC$1:$AC$1000&$AD$1:$AD$1000&$AE$1:$AE$1000&$AF$1:$AF$1000&$AG$1:$AG$1000&$AH$1:$AH$1000&$AI$1:$AI$1000&$AJ$1:$AJ$1000&$AK$1:$AK$1000,0))

    Still confirmed 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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Match Cell date from Column and return value of third column's Cell
    By Tieddyekid in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-16-2017, 12:19 PM
  2. Match Cell Substrings to Column and Return Adjacent Cell
    By ashb444 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-06-2015, 11:24 AM
  3. Replies: 12
    Last Post: 07-10-2015, 01:18 AM
  4. Replies: 8
    Last Post: 06-25-2014, 05:43 AM
  5. Match Cell Fill Color to another cell in same column in Array?
    By Trevorrow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2013, 10:06 AM
  6. Replies: 1
    Last Post: 08-02-2012, 11:39 PM
  7. Need to look up cell and spit out specific words
    By nduddy in forum Excel General
    Replies: 1
    Last Post: 10-28-2011, 07:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1