+ Reply to Thread
Results 1 to 7 of 7

Lookup or Index Formula

  1. #1
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Lookup or Index Formula

    I have some data reports that I am responsible for, and in a time crunch, so I am trying to find away to make it a little easier to get the data I need. I am having problems uploading the doc, so I will do my best to explain what I would like to make happen, may not be possible but here goes. On Sheet one I have a list of Cities in Column C, WAS, STL, CHI, OAK etc. Also in Sheet one, in Column K, I have either HF, HD, AF, or AD depending on certain criteria. In Sheet 2, in Column R, I have all the citites listed again, and then I have seperate Columns for HF, HD, AF, and AD, They are AK though AN respectively. These cells in these columns can either be blank, or will have POS or NEG in each. The blank just respresents that no data came in that day. Ok now for what I 'd like to be able to accomplish, but not sure it's possible. Somewhere on Sheet 1, I would like a formula to look at the city name, and then if it's HF, HD, AF, or AD. Then go into sheet 2, find that city in Col C, and then the corresponding HF, HD, AF or AD column, and return the last 5 data filled cells. I think it's probably asking to much. So as an example: On Sheet 1 STL is in Colum C, HF is in Column K, go to sheet 2 column R, find STL, find look in Col AK for HF, and then return the last data occurences starting from the bottom of the HF Column going towards that top. The top of the column is later dates. Sorry if it sounds complicated, I am trying to upload a doc to make it easier. I think it's asking too much from excel though.. but thought I would check. I realize that if the data cant be returned to sheet 1, I am guess it would have to spread over 5 consecutive cells..

  2. #2
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Lookup or Index Formula

    sorry one more note.. I don't need blanks returned.. I only want the last 5 occurnces where POS or NEG appeared..

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup or Index Formula

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Lookup or Index Formula

    This is a quick mock up... in the example I used SEA as the example. On sheet 2 I filtered by SEA to make it easier to see where I wanted the data to come from. On Sheet 1 listed how I figured it the returned data would have to display. Note: The data in sheet 2 will continue to be added, so in the SEA example, if there is an update tomorrow, and something appears in the HF column, I would like the data on Sheet 1 to reflect the new data with the newest data going under the 5
    Attached Files Attached Files

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup or Index Formula

    See attached.

    Formula in L3:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER


    Note: with Array formulas you cannot use whole column references.

    Replace the $1000 occurances in the formula with a row number that you know is above what you will ever need... but be careful not to make it too large, as performance will be reduced.

    Also note, that I used Conditional Formatting to hide the #N/A that occur when there are no values in column K to match and when there are less than 5 entries made for a specific Effort....

    If there are less than 5 entries for the Effort, then the items are filled from the right side of the table... to go from left would require more array formulas...which equals less efficiency.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Lookup or Index Formula

    This works great! Thanks a ton.... the only think I don't get is the confirmed piece.. when I move my data into the dummy spread sheet it works.. but when I try to copy the formula into my already prepared one.. it returns all NA's.. I tried hitting ****-CTL-ENTER.. but I am not familiar with that. I had to change some of the formula to match the correct columns etc.. it nothing else.. I will just add my data to the dummy one and work around the formula since it works! Thanks again!

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup or Index Formula

    once you have typed or adjusted the formula in any way, you hold the CTRL and SHIFT keys down. Then hit ENTER. You should see a pair of curly { } brackets surround the formula. If so, you have done it correctly. Now you copy the formula across the 5 columns and down the entire table.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1