+ Reply to Thread
Results 1 to 5 of 5

If statement with multiple lookups

  1. 04-04-2007, 06:04 PM #1
    elew69811
    elew69811 is offline
    Registered User
    Join Date
    12-15-2006
    Posts
    66

    If statement with multiple lookups

    I am trying to return the text in column d and e onto a seperate worksheet if column a cell in column a has the word "stop" in it. column a can have the word stop many times on different rows so the fisrt time it finds it (as an example in A4 it should return what is in cell D4 and E4 cells F7 and G7 in the other worksheet. Then if it finds the word stop in cell A9 it should return what is in D9 and E9 to F8 and G8. Hope this makes sense I am frazzled trying to figure this out. Thanks
    Reply With QuoteRegister To Reply
  2. 04-04-2007, 06:19 PM #2
    VBA Noob
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Prehaps something like the below array forumla (Ctrl + shift + Enter)

    Drag down for 2nd and third criteria

    =INDEX(B1:B10,SMALL(IF(A1:A10="Your Criteria",ROW(A1:A10)-MIN(ROW(A1:A10))+1),ROW(A1)))

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!
    Reply With QuoteRegister To Reply
  3. 04-04-2007, 06:23 PM #3
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Assuming your data is in sheet1! try this formula in F7 in your other sheet, copied to G7 and then down both columns

    =IF(COUNTIF(Sheet1!$A$1:$A$100,"stop")>ROW()-ROW(F$7),INDEX(Sheet1!D$1:D$100, SMALL(IF(Sheet1!$A$1:$A$100="stop", ROW(Sheet1!$A$1:$A$100)-ROW(Sheet1!$A$1)+1),ROW()-ROW(F$7)+1)),"")

    confirmed with CTRL+SHIFT+ENTER

    Note: if you do this correctly curly braces like { and } will appear around the formula in the formula bar
    Last edited by daddylonglegs; 04-04-2007 at 06:26 PM.
    Reply With QuoteRegister To Reply
  4. 04-04-2007, 07:30 PM #4
    elew69811
    elew69811 is offline
    Registered User
    Join Date
    12-15-2006
    Posts
    66
    Working with both formulas to see if I can get them to work. They are a little advanced for me but I am enjoying the challange and the learning experience. Thanks to both responders!
    Reply With QuoteRegister To Reply
  5. 04-05-2007, 08:09 PM #5
    elew69811
    elew69811 is offline
    Registered User
    Join Date
    12-15-2006
    Posts
    66
    Well as I mentioned yesterday it was a challenge for me to apply it but your formula worked perfectly Daddylonglegs. It took me so long that I never even got a chance to try the one VBA Noob sent but also thanks to VBA. One more challange how do I enter an if to this formula that would be if a certain date range was in a column. If this is possible could I use a range for the dates like 90 days between x and y or would I have to list the dates. Thanks in advance
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1