I am attempting to make excel search a column of random values to find the value i want, and then tell me what cell or row it was found in... the ultimate goal being to make excel copy the name (heading) of the respective row the result is found in, into this new spot... Sorry if this is kind of confusing, any help would be much appreciated. Thanks.
Last edited by VBA Noob; 10-13-2008 at 02:22 PM.
If your headers/labels are in column A (rows 2 to 500) and your data is in column B, with the value to be searched in B1, then this should work for you;How's that?
Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
sorry for the long response time...was out of town.
but it didn't quite work. i want to essentially tell excel the value i want it to look for (ie. 9) and tell me where that value is found, and then take the corresponding heading from the left hand side, and copy it into this new space... the formula seems to give me the headings, which is good, but in the same order that I had put them... and i need them arranged from smallest to largest.
bump please...
Say your table is in A2:F10 with row headers in A2:A10 and your lookup value is in X1, then try:
=INDEX($A$2:$A$10,SUMPRODUCT(($B$2:$F$10=X1)*(ROW($A$2:$A$10)-ROW($A$2)+1)))
Adjust ranges as necessary.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks