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:
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.
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.