0

In a Google sheet I have to search for a column containing a specific number. My search is

=Query(OPGAVE_TRANS!$A$1:$AD$23;"select AD where A="&A1&" OR B="&A1&" OR C="&A1&" OR D="&A1&" OR E="&A1&" OR F="&A1&" OR G="&A1&" OR H="&A1&" OR I ="&A1&" OR J="&A1&" OR K="&A1&" OR L="&A1&" OR M="&A1&" OR N="&A1&" OR O="&A1&" OR P="&A1&" OR Q="&A1&" OR R="&A1&" OR S="&A1&" OR T="&A1&" OR U="&A1&" OR V="&A1&" OR W="&A1&" OR X="&A1&" OR Y="&A1&" OR Z="&A1&" OR AA="&A1&" OR AB="&A1&" OR AC="&A1&" ")

Is it possible to write this criterion in a shorter way?

Thank you! I'm sorry to have given too little information. I have in a spreadsheet OPGAVE_TRANS in the area A1:AC23 placed each of the numbers from 1 to 224. The numbers are placed in random order. There are no duplicates, but since there are more cells than numbers, there are empty cells. In AD1: AD23, the letters from A to W are arranged alphabetically. Overall, the task is to find the address of the cell in which each of the 224 numbers is located!

In the current spreadsheet, in which the query is placed in B1, in A1:A224 I just have the numbers 1 to 224 in order. The search is intended to find the column in which the content of A1, in this case the number "1", is placed. The query thus delivers one and only one response value! I need other 223 queries built in the same way as the one shown to handle the numbers from 2 to 224.

In addition to these 224 queries, I have 224 queries to find the row in which each of the 224 numbers is placed.

So I use 448 queries to find the address of each of the 224 numbers. Can it be made easier?

Thanks for trying to help, but the problem is more complex. To make it simple, let's assume that the numbers from 1 to 18 are placed as follows:

enter image description here

In column G I have placed the numbers from 1 to 18, and in column H I want the address for each of the numbers in the data area. The indicated method does not solve the task - as far as I can see.

Improve this question
2
  • Welcome to Web Applications. Please add some sample data
    – Rubén ♦
    Nov 12, 2021 at 23:17
  • 1
    At a glance, 1.) yes, it can be condensed; 2.) it's not clear to me what should be happening or how many rows of results you intend to be returned; 3.) you're including A1 in the return range and the search range, which doesn't seem like that would be what you want. As Rubén suggests, please either add realistic sample data or (better still) share a link to your spreadsheet (or to a copy of it), manually entering the results you'd like to see where you'd like to see them.
    – Erik Tyler
    Nov 12, 2021 at 23:42

1 Answer 1

Reset to default
0

Rather than use a query, there is a simple and elegant solution using SUMPRODUCT.

  • Enter this formula in Cell H2 AND copy down as many rows as values in Column G

=ADDRESS(SUMPRODUCT(($A$2:$E$6=G2)*ROW($A$2:$E$6)),SUMPRODUCT(($A$2:$E$6=G2)*COLUMN(OP!$A$2:$E$6)),4)

Ideally one would like to apply an Arrayformula here but SumProduct and arrayformula don't play together. Possibly (though not defintely) there is an option using MMULT but this is left to the OP to investigate.

With the benefit of hindsight, this was an example of an XY Problem.

Sample

screenshot

Props: @YinCognyto Formula to find match in two-dimensional range in StackOverflow.

Improve this answer
2
  • Thank you very very much. It was a very smart and simple solution - ingeniously devised! Now my problem is solved in a beautiful way!
    – Knersoe
    Nov 17, 2021 at 22:21
  • I can't take the credit, but it does prove the value of research. In any event, if you find the answer helpful, perhaps you will consider "accepting" it.
    – Tedinoz
    Nov 17, 2021 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.