Find and return cell address

R

RHH1095

New Member
Joined
Dec 3, 2011
Messages
12
I am looking for a function that will search a group of cells (Say -- A1:D15) for a specific value located in another cell (Say -- E15) and return the Cell Address where the match was found from within A1:D15.

The values in the "array" A1:D15 can be both text and numbers.

Thank you in advance.
RHH1095
 
Sort by date Sort by votes
Thank you WarShip. I will take a look at this code. I never define a function before some this will be interesting.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi WarShip,
The code seems to partically work. I believe is it compare the value from E15 to the values in the array (A1:D15). However I would like to to report back the address of the cell which matches E15. It seems top either report back the value within the cell or just the value from E15.
What do you think?
RHH1095
 
Upvote 0
You're right.
Replace:
Code: 
Set found = Range(rng).Find(srchVal, , , xlWhole)
With:
Code: 
Set found = Range(rng).Find(srchVal, , xlValues, xlWhole)
 
Upvote 0
Hi Robert, Looks like I could use a little more help. If this is possible.

Is there a way to drag and copy and automatically adjust cell references for the following expression as I describe below.

This formula is in Cell A7...
=ADDRESS(MIN(IF(A1:F6=A13,ROW(A1:F6))),MIN(IF(A1:F6=A13,COLUMN(A1:F6))),4)

When I drag it to Cell B7 I would like it to become....
=ADDRESS(MIN(IF(G1:L6=B13,ROW(G1:L6))),MIN(IF(G1:L6=B13,COLUMN(G1:L6))),4)

As you can see I would like A1:F6 to adjust to G1:L6 (By 6 cells) and A13 adjust to B13 (By 1 cell).
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
This one is tricky. VLookup but return a different cell value
Replies
6
Views
228
Alex Blakenburg
A
R
  • Question
VBA Check if value in Sheet1 exists in Sheet2 then copy specific cells from Sheet2 into Sheet1
2
Replies
13
Views
758
dermie_72
D
ChristineJ
  • Solved
Return cell address based on rank of value
Replies
8
Views
207
Peter_SSs
Peter_SSs
B
  • Solved
Returning entire rows from multiple sheets into array based on lookup values in multiple columns
Replies
1
Views
471
KRice
K
H
  • Question
Search multiple cells for data "containing" a text string...then return the information from that cell
Replies
2
Views
116
Hat4Life
H

Forum statistics

Threads
1,215,212
Messages
6,123,653
Members
449,111
Latest member
ghennedy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top