hi there,
how can i look up a single value in table of values n then get a return of some sort either telling me it exists or telling me which cell location that value is in.
126 126 111 215 215
363 232 179 188 893
117 243 643 534 334
243 581 192 138 622
this is a typical table with values and i want excell to be looking up 215 in this table and telling me the cell location.
help please!
thanks
Last edited by abz; 06-09-2010 at 01:12 PM.
How about conditional formatting.. to highlight the existing values.
So if your table is in say A1:D5, and your lookup value is in F1...
Then select A1:D1 and go to Format|Conditional Formatting and select Cell Value and then Is Equal To from drop downs and enter formula:
=$F$1
Then click Format and choose colour or effect from the Font or Pattern tab.
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.
Well that would only help if its a small table of values, i have a massive one with about 600 rows and about 30 coloumns.
And secondly this would only hightlight/format the value with in the table it wouldn't return anything in another cell, which is what i am looking for to get an indication that the value i am looking for exists in this table.
Just to mention i want to search for about 40 values in that table so it wouldn't be ideal to like define a condition for each value.
Thanks
=if(Countif($A$1:$D$100,X1),"Value exists","Value doesn't exist")
this will advise you if the value in X1 exists in table defined by range A1:D100...
adjust as necessary and you can copy down.
Thanks alot,
that does now tell me if the value exists, now is there a way to for it to tell what cell location it is in?
Thanks
Since your tables are big and you have possibility of the number appearing multiple times, perhaps it is better to use VBA to get what you need...
.. if that is an option for you, then say so, and hopefully someone with better VBA expertise than I can help you.
Yeah VBA is what i was expecting initially but if a combo of built in excell formulae could do the job, i would be fine with that.
Anyway thanks a lot for you help!
So anyone can help me with a vba for my problem?
See attached mock-up.
Please Login or Register to view this content.
Stephen,
It looks like your code only returns 1 matching cell reference...
I think the OP would have possibly multiple entries of the same value and so would need address of each match....
NBVC - thanks, you're absolutely right. I meant to ask about that but forgot. Will update shortly.
Please Login or Register to view this content.
Last edited by StephenR; 06-09-2010 at 11:04 AM.
Thanks mate, it works
Phew, glad to hear it.
Thanks for the assistance Stephen.
Here I am late as ever!
As I have made up a sheet, here it is
This workbook counts the instances found, Highlights the cells and lists the cell addresses, was going to add hyperlinks, but alas pipped at the post
However, better late than never
Cheers
P.S. Just enter a value in C3
Last edited by Marcol; 06-09-2010 at 11:55 AM. Reason: P.S. added
That one looks really pretty, however would it work if i have the cell in one sheet and the actual table in another sheet?Originally Posted by Marcol
Try this,
Of course we would have to know the first cell address of the matrix/table This example works on A3 therefore do not delete the data in Col A & Row 3. other than that it is dynamic based on the last data in col A & Row3.
Of course we could hard define the table if you want.
The amended code is not pretty and not ugly...it's just pretty ugly!!!...
It's just a quick fix to demonstrate the principle
And in the worksheet module for sheet1Please Login or Register to view this content.
Please Login or Register to view this content.
P.S.
It also highlights blanks in the table.
Hope this helps.
If you need any further information, please feel free to ask further
However
If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
Thanks a lot for that Marcol, actually thanks to all who helped!
This is a great place to learn will defo keep coming here!
:D
Hello everyone, and special thanks to Marcol for the macro.
I have the same question and want to find the location of the value in the entire table, but my table is much bigger than the file you provide.
I am not that familiar with VBA coding, how can I specify the row and column number of my table? for example, can I write the row and column number of my table in specific cells in sheet1?
Try this,
Of course we would have to know the first cell address of the matrix/table This example works on A3 therefore do not delete the data in Col A & Row 3. other than that it is dynamic based on the last data in col A & Row3.
Of course we could hard define the table if you want.
The amended code is not pretty and not ugly...it's just pretty ugly!!!...
It's just a quick fix to demonstrate the principle
And in the worksheet module for sheet1Please Login or Register to view this content.
Please Login or Register to view this content.
P.S.
It also highlights blanks in the table.
Hope this helps.
If you need any further information, please feel free to ask further
However
If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
Hi.
It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).
A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.
Thread closed.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks