Excel 2007 : multiple indentical lookups required from table array

I am need of assistance with scenario I am trying to create in Excel 2007. I am a bit familiar with the vlookup command but I need to do something that is a little more robust.
I have a table with approx. 8 columns and 35 or so rows. The data in this table will change daily except for column A which will have same character strings daily but will be in different row locations of column A daily. To clarify: imagine the names of colors being in Column A (Red, Blue, Green, Yellow, etc.). As stated, these colors will remain constant as far as the table is concerned but their daily location will change in Column A (ie. Red could be in A1 one day and A4 the next). This of course is where vlookup comes in, whereby it will search the column and report the values of the corresponding columns chosen for the respective color found.
Now here comes the problem. I am going to have occasions where the Column A value that I am looking for is going to appear 2 or 3 times in Column A at various locations. (ie. Column A values: Black, Red, Blue, Yellow, Red, Yellow, Green, Black, Red).
What I am needing to do is locate the first required value (ie. Red) and report the other column values that correspond to the column/row that the first Red value was found. Then another cell containing another lookup for the second occurence of this same value (ie. Red) and the corresponding values from the columns where this value is found.
I am not familiar with ganging multiple arguments and whether or not you can use a vlookup command in conjunction with say a lookup or find command.

thank you in advance for your assistance.

Okay.....hopefully the attachment will give you some idea as to what I am up against.
Sheet 1 will be a typical copy and paste scenario from another work data base.

Sheet 2 - I then rearrange Sheet 1 so that the vlookup information is in the first column where I need it. As you can see, I have highlighted a couple of the numbers that repeat themselves in this first column but the numbers that are associated with them vary in the corresponding columns.
I have done a few vlookup queries for one of the entries (far top right of Sheet 2 highlighted in orange).
Now the problem I have is that this works for the first entry but how do I have Excel locate the second and third entries and deliver the corresponding information from those location's columns via vlookup or some other method.

thank you in advance for your assistance.

Hi DILIPandey,
It appears on the surface that you certainly have established what I am looking for. Obviously complicated and may be beyond my capabilities. Can you explain what exactly the formula is and does and how you arrive at the result?
I tried changing the number in the formula after ROW in an attempt to source other data from other columns, however that didn't work. You've used columns 2, 6 and 13 as I did in my vlookup example, but other columns may be required to be reported.

Thank you very much for your assistance.

Should I start another thread for another query or can you help me with it too?

Sincerely
tornado26tiger

Hi DILIPandey,
this is looking pretty promising. I had created Sheet 2 by transferring and rearranging Sheet 1's information. But that was only for the purpose of using vlookup which requires the column 1 information to be in that position for lookup. If you look at Sheet 1 you will see that it essentially has the same information except the column names are there. So what would the formula look like if you were to use the information on Sheet 1 and use the column names to, as you say "eradicate this exercise"?
Also I may want to use your formula on one sheet (ie. Sheet 3) but the data to be searched will be on Sheet 1 (as per my request above). What will that formula look like if I am reporting the result on a different page than where the table of data is located?

Thank you very much for your assistance!

tornado26tiger

Hi DILIPandey,

Awesome!!!!!!! Well, awesome for you. I still have to understand it and hopefully I can make it work for myself. I tried your previous version a couple of times. I must be doing something wrong as I couldn't get it to report back like you did.

3 Questions for you.
1) What does the SMALL do in the whole function process?
2) What does the -2 do (ROW($A1))-2) do as well?
3) The table array (Sheet 1) is going to change with respect to size (rows only) daily put the other sheets that I am going to report the data to are going to remain fixed. Can I expand the table array (currently A3....Q46) to something that will ensure I capture the information (say A3......Q150) without disrupting the function even though there could be many empty rows within this size of array?

I get most of the other syntax as far as sourcing the column that I am looking for the matches in and the table arry that it is searching through etc. Just going to have to play with it a bit more and get some results.

Once again I thank you immensely for your assistance with this. This is going to save me a lot of time in the future once I get the proper spreadsheet built.

Sincerely
tornado

Ha! That is just ducky!
Now you've created more questions.

1) So is the answer to my question about simply expanding the search area (currently A3......Q46) to (A3....Q150) not possible with the blank cells in that size of array. Yes/No?

2) I see you have the ColData instruction now in the function. I see by clicking on ColData in the Name Box that you have assigned the Column Names/Headings into this name. I assume this is what you meant by creating a dynamic name. However, once again, I do not know how to accomplish this.

Thank you for your patience.

Sincerely,
tornado

oops.....forgot a question.

I am assuming copy and pasting or right bottom corner auto fill is the best way to duplicate the fomula as I am going to require it in a large number of cells to return date. Yes/No?

Thanks
tornado

Hi Dilipandey,

Well, I have been messing around with your latest spreadsheet for the last 3 hours and I haven't had much progress/success. yikes...i am truly an amature.

I have attached the file that I have been working with. I took your file and added pages etc. because I wasn't able to figure out how to create a dynamic name scenario like your Coldata so I sort of cheated by just using your example.

Problems - in the true environment that i am going to be using this spreadsheet in, the On Order Copy & Paste sheet is going to have 5 rows of other various information that isn't important to the function (I have just left them as blank for now). As you can see your formula on the On Order Worksheet page doesn't report back the correct information because of the 5 rows that I added on the On Order Copy & Paste sheet.

Problem 2 - prior to me adding the 5 rows and when your version was untouched, I attempted to copy and paste your formula into cells directly below the ones that you had input. Then I put another number into Column D (or B in the original version) and altered your formula to reflect D6 cell (or B6 cell). Used the control+shift+enter function to implement the {} brackets for the function but nothing seemed to work.

Please advise on what I am doing wrong.

Thank you in advance for your assistance once again.

sincerely,
tornado