+ Reply to Thread
Results 1 to 6 of 6

Formula to idnetify if cell value is in row of data according to row identifier

  1. #1
    Registered User
    Join Date
    11-19-2013
    Location
    Surrey, England
    MS-Off Ver
    2010
    Posts
    5

    Formula to idnetify if cell value is in row of data according to row identifier

    Probably the most confusing thread name ever, I know, but I need help with a formula which is blowing my mind and I'm starting to doubt whether it's even possible.

    Here's my scenario - I have a list of policy numbers in column A, each with a corresponding premium amount in column B.

    On a separate tab (which I'll call tab 2), I have a list of policy numbers in column A and each policy number has a monthly payment amount in columns B to M (headed January to December). Unfortunately, the policy list is far more extensive in the tab 2 than it is in tab 1, so the two tabs don't contain the same list of numbers in column A.

    What I need to do is, using the policy number in column A of the tab 1, look up whether the premium amount in column B of the tab 1 appears in columns B to M of the row containing the corresponding policy number in the tab 2. If it does appear at all in the corresponding 12 columns of tab 2, I need to return a value of "Yes" in column C of tab 1, otherwise I need it to have a value of "No".

    Just writing that out gave me a headache and I've tried a few things, with absolutely no success. It's the lookup aspect that's beating me, so I'm not sure if it's possible, but if anyone could give me any pointers, I would be hugely grateful.

    Thanks.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to idnetify if cell value is in row of data according to row identifier

    Can you post a SMALL sample file so we can see the data structure?

    Don't need 1000s of rows of data. 20 rows is plenty. Make sure you show us what results you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    11-19-2013
    Location
    Surrey, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Formula to idnetify if cell value is in row of data according to row identifier

    Dummy data.xlsx

    Thanks for responding. Here's some dummy data in the format I'm using. Hopefully this will make what I was trying to explain a bit clearer.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to idnetify if cell value is in row of data according to row identifier

    Try this...

    Entered in C2 and copied down:

    =IF(COUNTIF(INDEX('Tab 2'!B$2:M$49,MATCH(A2,'Tab 2'!A$2:A$49,0),0),B2),"Yes","No")

    A couple of your expected results are incorrect.

    Row 5 should = Yes
    Row 15 should = Yes

  5. #5
    Registered User
    Join Date
    11-19-2013
    Location
    Surrey, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Formula to idnetify if cell value is in row of data according to row identifier

    Fantastic. Thank you so much for your help, Tony.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to idnetify if cell value is in row of data according to row identifier

    You're welcome. Thanks for the feedback!

+ 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. Button to pull data from another area based on active cell as identifier
    By gotjobs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-19-2014, 02:49 AM
  2. [SOLVED] Extrapolating multiple occurences of data from cell using ( ) as an identifier.
    By mstubbs in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-11-2012, 01:40 PM
  3. Macro to move data from one cell to another where unique identifier changes
    By boothyuk123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2012, 05:36 PM
  4. Combine rows of data into 1 cell based on unique identifier
    By jud_goh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-11-2010, 05:06 PM
  5. Replies: 1
    Last Post: 12-07-2009, 07:02 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