+ Reply to Thread
Results 1 to 3 of 3

Not sure how to ask this. Using values from 2 different columns to find their intersection

  1. #1
    Registered User
    Join Date
    01-10-2019
    Location
    Austin
    MS-Off Ver
    365
    Posts
    1

    Not sure how to ask this. Using values from 2 different columns to find their intersection

    So I am putting together a FMEA and want the values from column I and column J to be used to look up their intersection on a table and return a value in column M.

    I thought about a index/match, but not sure how to do that, if column I has any "Letter" (A,B,C,D,E) and column J any "Numeral" (I,II,III,IV,V), take those identifiers and find the intersection in table and return the value of that intersects into column M.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Not sure how to ask this. Using values from 2 different columns to find their intersec

    Probably the best thing to do is upload a small sample workbook with a representative sample and your expected results. If there is confidential information contained remove it, it should be enough data so people can see what you are dealing with.
    the way to upload a workbook is to use the "go advanced" button below this window, then scroll down and you'll see a blue hypertext that says "manage attachments" click on that, then browse to your sample. I find it easiest to save it to my desktop so I don't have to dig into files.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    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: Not sure how to ask this. Using values from 2 different columns to find their intersec

    Hi, welcome to the forum

    Agree that a sample WB would make things easier.

    However, I feel you are on he right track with INDEX/MATCH/MATCH, providing you are referencing a table that has eg, letters going down and numerals across the top (or vise versa) See if this will help?

    INDEX returns a value at the intersect (meeting) of a row and a column.
    INDEX() syntax is =INDEX(range,row-num,column-num)
    so something like =INDEX(A1:J10,3,5)
    will return the contents of E3 (row 3, column 5)
    Didnt even use MATCH, did we?

    Now, to find the "3" or the "5", we would use the MATCH function...
    MATCH syntax is...=match(criteria-to-find,column-to-search-in,0) 0 returns exact match
    =MATCH("cc",A1"A10,0)
    If "cc" is in A3, this will return 3
    (to find the column number, we use the same thing, just change the range=MATCH(criteria-to-find,row-to-search-in,0)
    '=MATCH("zz",A1:J1,0)
    If "zz" is in E1, then this will return 5

    Put them all together and you have...
    =INDEX(A1:J10,MATCH("cc",A1:A10,0),MATCH("zz",A1:J10))
    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

+ 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. find intersection and delimit the values
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-10-2016, 10:23 AM
  2. find intersection and delimit the values
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2016, 11:17 AM
  3. find intersection and delimit the values
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2016, 11:17 AM
  4. Replies: 1
    Last Post: 04-19-2013, 08:30 PM
  5. VBA to find the range at the intersection of two values.
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2013, 06:10 PM
  6. Replies: 2
    Last Post: 01-12-2012, 01:25 AM
  7. find a value at an intersection?
    By Ellen G. in forum Excel General
    Replies: 4
    Last Post: 06-29-2006, 03:40 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