+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Lookup:complex excel

Hybrid View

  1. #1
    Registered User
    Join Date
    12-22-2010
    Location
    East Coast, USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question Lookup:complex excel

    Hello everyone - I have a complex lookup formula problem that I hope someone can help me with.

    I’m working with a complex excel workbook that I’m trying to build out to include more visibility, and I’m stuck with “writer’s block” on an issue because I’m not an advanced user of combo formulae in Excel (which I think would solve the problem).

    Here’s what I’m trying to do:
    I want to insert a column that will indicate a value that is looked up in another cell using a formula that I can insert into multiple rows.

    Here’s the problem:
    At the top of the workbook, in the first 3 rows are fixed data elements/titles that follow the following pattern:
    Row 1: space
    Row 2: space
    Row 3: date in the format of Month/Year (this is currently only listed above DE 2 below, but I can adjust to include an invisible/white date above each DE for simplicity)

    Row 4:
    Column 1: Data element 1 (DE1)
    Column 2: DE 2
    Column 3: DE 3
    Column 4: DE 4
    The pattern described above is repeated across 24 months

    Now, the above pattern starts about 10 columns into the workbook as the first 9 columns contain a list of items we are tracking. One of these columns contains a value for the item that is plotted in one of the 24 months (in the columns to the right) in the SAME row. Here is an example of the pattern:
    Column 1: Task #
    Column 2-4: other identifying task information
    Column 5: Description of task
    Column 6: Amount
    Column 7: The column I want to insert: date that the amount is plotted in to the right

    For corresponding row items under column 7 (which I can move before column 6 if need be) that have a value, I want to look up values in column 6 in THAT row all the way to the right and when the value is found, it should display the date in the fixed row# 3 (and with a simple modification, will be in the same column that the value is found in).
    I’m thinking because the date row (row 3) is fixed, and for each given item I’m doing a look up on, the row is known, there has to be a way to do a combo lookup formula that will find the value and display the date.

    There are a couple other variables here that have prevented me from using a simple search for value or v/hlookup:

    -If a given amount is not present under one of the dates, there are $0 (which need to remain there)

    -The value may show up twice in the same row, sometimes under a different date pattern, and I need the date shown to be the FIRST time the value shows up for

    -Given the above, there are 3 values in a given row after the display column I’m trying to insert: 0s, blanks, and the value that’s being searched for (sometimes listed twice)

    -Because values may be the same for other items, I can’t do a simple search in the entire workbook – it has to be specific to the relevant row item

    -Values aren’t necessarily always ascending

    While I’d love just a solution to the display column above, the optimal solution would be a display/lookup formula that colors the cell green if the value is listed twice (which indicates the item is “finished”) and yellow if it’s listed only once (which indicates it’s “open” or wip).

    Any help would be greatly appreciated!!

    Thanks,
    Mike

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    re: Lookup:complex excel

    Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

    Manually mockup your desired results and point out specifically those cells where you've done that and clarify how you go that if it isn't patently obvious.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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