2

I want to check where - in a specified range - does a value exist in a Google Sheet. If I already know the column, I would use the "Match()" function to get the row number. But let's say if I still want to get a number for the column, and use the Match() function again, I wasn't able to. In the match function, I wanted to use a dynamic value like "Sheet1!E3" to specify the row I'm searching for, but it didn't work. Here is the sheet to illustrate the idea. Why is that? In a case like this, if I want to use the dynamic value twice - Sheet1!E3:Sheet1!E3, what should I do instead?

Thanks!

See the test sheet linked

player0
  • 124,011
  • 12
  • 67
  • 124
Peiran Yu
  • 43
  • 6

1 Answers1

2

delete E4. use in E3:

=INDEX(FLATTEN(SPLIT(TEXTJOIN(, 1, IF(E2=A2:B17, ROW(A2:B17)&"​"&COLUMN(A2:B17), )), "​")))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks so much @player0! This is exactly what's going to help! ----- However, I tried to understand how it works but couldn't really.. any relevant documentations on these you would recommend me read to understand better? I tried to understand it piece by piece, but I got all errors when I dissected them up. For example, I tried "=IF(E2=A2:B17, ROW(A2:B17)&"​"&COLUMN(A2:B17), )" and wanted to see what just this would return but got error already. – Peiran Yu Nov 29 '22 at 03:16
  • @PeiranYu for processing arrays/multi-cell ranges you always need ARRAYFORMULA wrapping. try to run it like: `=ARRAYFORMULA(IF(E2=A2:B17, ROW(A2:B17)&"_"&COLUMN(A2:B17), ))` – player0 Nov 29 '22 at 09:45