Search for a value in a table an return separate x and y coordinates of all its locations

I would write a custom function for this. Is a UserDefinedFunction (vba) ok? Not everyone likes macros enabled in their workbooks.

So I do it correctly, can you provide a sample workbook with a demonstration of the data and a manual mockup of the results you're trying to achieve?


Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Here's a UDF that you can add into your workbook:

Please Login or Register  to view this content.

How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.

In a cell:

=XY(TableRange, ValueToMatch, InstanceToReturn)

In the attached sheet, I put this into L3 and copied down:

=xy($A$1:$C$14, $J$3, ROW(A1))

The ROW(A1) is just a trick that resolves to "1" so it returns the first match found. As that formula is copied down that part increments and gives you the next match.

The formulas in M:N split that string out, create your chart from those columns.