identify cell values in excel 2010

[sapen]

Hey Excel Maestros,

I have a quick question with identifying the values sitting in cells.

I have 2 columns in Sheet1. Column A has numbers listed from 1 to 20 sequentially. Column B has random numbers listed with duplicate values. Now if I pick a number from column B (lets say a 25 and it sits in cell B5 and B11 and B19) and pass it to a function then that function should return me the corresponding values of it with respect to column A that is the values sitting in cell numbers A5, A11 and A19 (which are 5,11,19 obviously). Also if I reference the number 25 to a different column lets say C1 and instead of passing 25 to the function I want to pass C1 even then it should return me 5,11,19.

Is a separate function required for this or is there something that already exists?

Thanks a bunch Again.

[Paul]

Hi Sapen,

The following UDF (user-defined function) can be put into a Standard Module, which will allow you to use the function =FINDME(cell) in your worksheet. For example,

=FINDME(C1)

Where C1 contains the value you want to lookup in column B.

Please Login or Register  to view this content.

[sapen]

Hi Paul,

Thanks for the reply. I added the function to my spread sheet and used it as =findme(cellnumber) and I got the output as #NAME? so I tried using it as =FINDME(value sitting in that cel lnumber) and it still returned #Name?

[Paul]

You added it to the spreadsheet, but did you insert it into a Standard Module (*not* the Sheet1, Sheet2, ThisWorkbook modules)? In the VB Editor window, click Insert > Module and paste the code there. It should work as intended.

Also note that it will only accept a cell reference, not a value. It would need changing if you wanted to use values instead.

[sapen]

Thanks for the reply Paul. I think its my bad for not being more clear.

The current values are listed in column M beginning from row number 6. The cell reference here is M1 and the values I want the function to return are on Column L starting from row number 6. So what I did was made changes to the function as shown below. I am not a VB guy .

Please Login or Register  to view this content.

Can you please suggest if these changes that I made are correct and if not where do I need to make changes to this function.

Thanks much

[sapen]

And I am passing M1 =FINDME(M1)

[Paul]

That seems like it should work. You would then use =FINDME(M1) in some other cell (like N1).

I put the numbers 1 through 20 in L6:L25, then various numbers in M6:M25 (some duplicates). If I put one of the numbers from M6:M25 into M1, N1 returns the proper references from L6:L25. If I put a number in M1 that does not exist in M6:M25 then N1 is blank.

[sapen]

Cool for some reason its working now...Thanks for your help paul...please let me know if I can reply back to the same thread if I needed to add more functionality or have this closed/solved and open a new one.

Thanks a lot

[Paul]

If you want to add more functionality I'd suggest starting a new thread. If understanding the request in that new thread would require knowledge of this one, please insert a link stating so.

Glad it's now working for you.

[sapen]

Sure Paul.. but can this functionality be extending to other columns as well? Say If I have values listed in columns M through Z from row number 6 to row number 1000. And I pass a parameter X1 or Z1 would it still return me the corresponding number from the column K? Coz I just tried and it works like a charm for column M alone when I pass M1 as parameter. But when I pass N1 (and it has a value) it returns wrong results. Please advise

[Paul]

Can it be extended to other columns? Yes. As it is currently coded? No.

If that was your ultimate intention it should have been stated in the first post. Please provide a sample workbook of your actual data and layout (or fake data representing actual data if it is confidential) and show what you want the result to be, where and why.

[sapen]

Paul,

I thought I could use the same function everywhere hence I didnot post my complete question. Apologize for that. But please find the spreadsheet attached.The data is in Sheet1. The output has to be listed in the cells as listed in the spreadsheet. Also Please note that per my requirement I have to collect and use data from year 1975 to the current year and the number of records will also be increased. I have also put an example in the spreadsheet.

Thank you so much for your help with this.

[Paul]

I believe this slight change will make it work for how your data is arranged. Instead of:

Please Login or Register  to view this content.

use:

Please Login or Register  to view this content.

"r.Column" will return the column number of the cell you put in the formula. So =FINDME(M1) will return column 13. 13 - 12 = 1. So the function will look for values in the column 1 to the right of column L (due to the .Offset(0, 1) bit).

If you put =FINDME(M1) into cell U6, you can then fill the formula to the right and it should work for the others.

[sapen]

Cool...Thanks Paul...Thanks Much...

[sapen]

You rock man

[Paul]

Glad I could help.