# Lookup or Index Formula

1. ## 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. ## 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. ## 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!

4. ## 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

5. ## 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.

6. ## 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. ## 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.

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

#### 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