+ Reply to Thread
Results 1 to 7 of 7

Formula for : two values to find in another sheet this words and copy the hole cell

  1. #1
    Registered User
    Join Date
    12-06-2017
    Location
    Austria
    MS-Off Ver
    2010
    Posts
    3

    Formula for : two values to find in another sheet this words and copy the hole cell

    Hello,

    i have the problem that i can't find the right formula...

    I have 2 values in different cells Example under Komponente there are a lot of components there are beginning in the A3 (Mapping Sheet and down with other values) & B1 (and C1 are pinned values).

    with this criteria there must insert the right cell that will be find under "Data" sheet in the Mapping sheet cell nr. B3.

    The necessary data will be find under Data.
    I have tried with two vlookups but doesn't work

    i have attached the example file.

    I hope i was clear what i need.

    Thanks a lot.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: Formula for : two values to find in another sheet this words and copy the hole cell

    Not sure what you mean but here is a start.
    Try this in B3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and fill down.

    A3 is not possible to find, there is an extra word in the middle (FAI).
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    12-06-2017
    Location
    Austria
    MS-Off Ver
    2010
    Posts
    3

    Re: Formula for : two values to find in another sheet this words and copy the hole cell

    The FAI is in the right Row.

    Your IT's not working...

    I search the Cell that contains: Außenbeplankung & FAI on the "Data" sheet and the solution it will be must insert in the mapping sheet in the B3 cell.

    Thanks a lot
    André

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,145

    Re: Formula for : two values to find in another sheet this words and copy the hole cell

    Would you manually add all the results you expect to see.

    This would no doubt be simplified if you were to accept a helper column in the Data sheet so that the descriptions could be truncated and aligned with those in column A on the Data sheet.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: Formula for : two values to find in another sheet this words and copy the hole cell

    Like the other helpers in this thread, I'm not completely sure what results should be returned, but I went with the following formula in B3, which should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

    =IFERROR(INDEX(Data!$B$2:$B$17,IFERROR(SMALL(IF((ISNUMBER(SEARCH(TRIM(MID($A3,1,FIND("-",$A3)-1)),Data!$B$2:$B$17)))*(ISNUMBER(SEARCH(TRIM(MID($A3,FIND("-",$A3)+1,100)),Data!$B$2:$B$17)))*(ISNUMBER(SEARCH(B$1,Data!$B$2:$B$17))),ROW(Data!$B$2:$B$17)-ROW(Data!$B$2)+1),1),SMALL(IF((ISNUMBER(SEARCH(TRIM($A3),Data!$B$2:$B$17)))*(ISNUMBER(SEARCH(B$1,Data!$B$2:$B$17))),ROW(Data!$B$2:$B$17)-ROW(Data!$B$2)+1),1))),"No Match")

    The formula can be filled right for your "status 33" column and filled down for the other rows. The formula looks for the first match that contains the text on each side of the hyphen and the text at the top of the column (e.g. "FAI"). This will work around the issue spotted by Jacc in post #3, but only if the text on either side of the hyphen in column A is unbroken when it appears in your list on 'Data'. If there is no hyphen in column A, it just looks for a text match. Take a look at the attachment to see if the formula is returning results as desired:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  6. #6
    Registered User
    Join Date
    12-06-2017
    Location
    Austria
    MS-Off Ver
    2010
    Posts
    3

    Re: Formula for : two values to find in another sheet this words and copy the hole cell

    Hi CAntosh,

    thank you for the answer.

    Your formal was the solution for FAI row and Status 33 row.

    So the topic is resolved.

    thank you very much for this solution.

  7. #7
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: Formula for : two values to find in another sheet this words and copy the hole cell

    Glad to help, good luck!

+ 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. [SOLVED] Formula - If find 3 possible words within a cell, return the word found?
    By nobodyukno in forum Excel General
    Replies: 4
    Last Post: 03-17-2017, 01:30 PM
  2. [SOLVED] Copy Values From Reference Cells Based On Words Of Another Cell
    By Francis Tom in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-31-2016, 05:48 AM
  3. Replies: 5
    Last Post: 01-14-2016, 12:04 AM
  4. Replies: 3
    Last Post: 11-28-2012, 04:00 AM
  5. Replies: 2
    Last Post: 11-09-2011, 02:03 PM
  6. making a max function for the hole values NOT in the range
    By Stoi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-16-2009, 01:36 PM
  7. [SOLVED] how to find and copy values on sheet 2, based on a list on sheet 1
    By evanmacnz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-2005, 05:06 PM

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