0

I have a table of text values that don't have any top or side headers. Is there a way to find the address of the cell in the area I'm searching?

The table of relevant values is from F7:AB18. There are blank columns in between each column of values. I have used VBA to do this, but this is something I'd like to know how to do with a formula as well, if possible. I understand INDEX & MATCH are used to find a position, but usually they have headers to assist.

Table of text values

Improve this question
2
  • 1
    You can use index/match on a multi-column range. Can you give an example of what you'd like to see given this data?
    – Mockman
    Commented Aug 24, 2021 at 4:12
  • @Mockman - I'm trying to retrieve the address of the cell from the lookup value. I have another range further down the tab that uses the UNIQUE formula to pull these text values from their respective tabs, then I use VBA to transfer them to that table. I suppose some combination of ADDRESS with some form of XLOOKUP or INDEX-MATCH would do it, but I can't get the right combo.
    – Starnes Student
    Commented Aug 24, 2021 at 6:27

2 Answers 2

Reset to default
1

Try this:

enter image description here

Formulas from cells O3:O6

{=SUM((D2:L14=O2)*1)}
{=SUM(ROW(D2:L14)*(D2:L14=O2))}
{=SUM(COLUMN(D2:L14)*(D2:L14=O2))}
=IF(O3=1,ADDRESS(O4,O5,1),"Multiple occurences")

Formulas in {} are array formulas.

There is all-in-one formula in O7 cell:

{=IF(SUM((D2:L14=O2)*1)=1,ADDRESS(SUM(ROW(D2:L14)*(D2:L14=O2)),SUM(COLUMN(D2:L14)*(D2:L14=O2)),1),"Multiple occurences")}
Improve this answer
1
  • I appreciate both answers, however this one allowed for a precise match in the event of similar text values. I found with using LARGE/AGGREGATE & FIND it would mix up MIAG with MIA.
    – Starnes Student
    Commented Aug 25, 2021 at 19:03
1

Not sure why you would want the address, but you can try (with your lookup string in H1, and myRange refers to the range to search):

=ADDRESS(
      AGGREGATE(14,6,FIND(H1,myRange)*ROW(myRange),1),
      AGGREGATE(14,6,FIND(H1,myRange)*COLUMN(myRange),1))

enter image description here

And it doesn't matter if there are blank rows/columns in myRange.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .