+ Reply to Thread
Results 1 to 2 of 2

Test for the presence of a value and, if so, if other cells in that row fall within range

  1. #1
    Registered User
    Join Date
    09-19-2017
    Location
    Kentucky
    MS-Off Ver
    2016
    Posts
    1

    Unhappy Test for the presence of a value and, if so, if other cells in that row fall within range

    I am attempting to check one list of roadways (each has a unique identifier) against a separate list. If that roadway on the first list is present on the second I then want to see if it also falls within the milepoints on the second list (for both lists I have a column with for the beginning milepoint and a column for the ending milepoint).

    The formula (located in cell CC3) I have that is checking for the roadway on the second list is this:

    =IF(ISNA(VLOOKUP('Sheet1'!CA3,'Sheet2'!Y:Y,1,FALSE)),"",VLOOKUP('Sheet1'!CA3,'Sheet2'!Y:Y,1,FALSE))


    I am attempting to write a formula in another column that will check to see if the milepoints from the first sheet fall within the milepoints on the second sheet. Here is the formula for that:

    =IF(AND(CC3<>"",F3>='Sheet2'!F2,G3<='Sheet2'!G2),"YES","")

    Column F is the beginning milepoint and Column G is the ending milepoint on both sheets.

    I realized that this formula isn't doing what I want since the second and third logic tests are simply looking at those respective cells on Sheet 2, not at the Column F & G values on the same row where it found the matching roadway unique identifier. How would I go about fixing this? I can seem to reason my way through it. Thanks for any help you can provide!

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Test for the presence of a value and, if so, if other cells in that row fall within ra

    Hello and welcome to the forum.

    A couple of things first:

    Vlookup is typically used to return a value from a multi-column range. The first column is used to look up the matching value and a cell from a column to the right of the first column is typically returned.

    The way you use Vlookup with a single column will return the value that you are looking for, but it can be done a lot more efficiently. If you find a match, you already know what the match should be, because it is the value in CA3.

    You can use the Match function to get the same result, like this

    =if(isnumber(Match('Sheet1'!CA3,'Sheet2'!Y:Y,0)),CA3,"")

    In your second formula you want to check the cells in the same row as the matched value. This is where Match can be very useful. It returns the number of the row where the match was found. With that number you can use Index to get the cell in column F or column G. For example =Index(G:G,4) will return the 4th cell in the range G:G.

    So let's build this

    =IF(AND(CC3<>"",F3>='Sheet2'!F**same row as match in CC3**,G3<='Sheet2'!G**same row as match in CC3**),"YES","")

    =IF(AND(CC3<>"",F3>=Index('Sheet2'!F:F,Match('Sheet1'!CA3,'Sheet2'!Y:Y,0)),G3<=Index('Sheet2'!F:F,Match('Sheet1'!CA3,'Sheet2'!Y:Y,0))),"YES","")

    Does that help?

+ 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] Counting Number of cells that fall within a date range not working
    By adam121212 in forum Excel General
    Replies: 3
    Last Post: 06-12-2017, 03:51 PM
  2. Add Cells Whose Date Fall Between A Specific Range
    By STBTC in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-24-2016, 07:31 PM
  3. Replies: 2
    Last Post: 09-29-2015, 01:33 PM
  4. Replies: 7
    Last Post: 11-10-2009, 09:42 AM
  5. Replies: 3
    Last Post: 08-08-2005, 06:05 PM
  6. [SOLVED] presence of SOMETHING in a range of cells
    By MD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2005, 01:06 PM
  7. Test for presence of chart on active sheet
    By Katherine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2005, 10:06 AM

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