CODEWITHSUNDEEP
google-sheets
Asaf Nevo
Asaf Nevo

Reputation: 11688

Google Sheet: Return row that contains text in one of the columns

I have rows with multiple columns.

One of the columns, named "Related Ids" includes a list of ids divided by free space:

**Related Ids**
1002520877
1002539382 1002539693 1002539817 1002539921 
1002547517 1002548241 1002548328 1002548435 1002548624 1002548699 1002548928 1002549096 1002549172 1002549323 1115684979 
976377326 976377666 1002568164 1002568348 1002568493 1002568928 1002569480 1002569587 1002569665 1002569754 1002569841 1002569909 1002570047 1002570165 1002570297 1002570630 1002570884 1002571117 1002571260 1002571411 1002571522 1002571610 1002571703 1002571820 1002571915 1002572074 1002572174 1002572266 1002572336 1002572399 1002572561 1002572660 1002572742 1002572808 1002572864 1006332304 1015162436 1018172863 1018606509 1020695595 1038955852 1041899813 1059747493 1071658289 1081708683 1086578399 1096352805 1115686039 1115710640 1131128026 1138221999 1142604786 1150158003 1157525719 1157525726 1161815097 
999716893 999718748 999718876 999719554 1005799039 1026285413 1034532337 1034535230 1128893071 
980980329 1002827394 1002827529 1002827847 
960494611 980954976 980957663 999671902 999672306 999672595 999672997 999673262 999673511 1002838623 1002838718 1002838799 1002838939 1002839065 1002839213 1002839372 1002839513 1002839650 1002839820 1002840063 1002840602 1002840888 1005820934 1005822753 1012099867 1045162941 1045164263 1045164387 1045164493 1077946135 1077946860 1124515803 1124515810 
1002861184 1002862507 1002867718 1054929134 
1002893576 1002893963 1002894124 1002894194 1002894343 1002896134 1002897357 1002897464 1002897592 1002897696 1003618502 1042620517 1081836576 1101493924 1171745247 
1002910675 1002911334 1002911530 1002911637 1002912133 1006260504 1028227869 1028429815 1101074079 1129678410 1161763116 
1003267808 1003308244 1003308803 1003309283 1003309492 1003309707 1003314409 1003318080 1003318508 1003318675 1003341785 1003351629 1003361633 1003364603 1003369286 1003372055 1003378150 1003385389 1003388117 1003389387 1045954289 1054276004 1065749930 1078700877 1089404866 1101490701 1112815066 1122854091 1126049378 
1003096140 1003098322 1003098434 1003098561 1003098770 1003098973 1056250001 1101504028 1126050950 1157039914 
1003395456 1003413746 1003414254 1003414573 1003415003 1003415429 1003416548 1123189032 1157033396 1169050456 
1002970869 1002971183 1002971389 1002971486 1002971748 1002973839 1002974051 1002974184 1002974623 1002974762 1002974918 1002975083 1026335626 1063077335 
1003252123 1003252583 1003252718 1003252903 1003253099 1003582353 
1003245012 1003595729 1026296420 1034567391 1055772993 1083600713 1101467508 1112820478 1131574038

How can I search for an Id that is included in this range (i.e. 1002862507, which is in the 8th row on the second place), and return the row number or another cell from the same row?

Upvotes: 0

Views: 2085

Answers (2)

Nikko J.
Nikko J.

Reputation: 5543

Try this formula:

=query({ arrayformula(row(A1:A16)), A1:A16}, "select Col1 where Col2 LIKE '%1002862507%' ")

Output:

enter image description here

Reference:

Upvotes: 2

Kay
Kay

Reputation: 99

First, if you have any ability to work with this data in a different way, I'd encourage it. This is a hacky solution. But, here it is:

  1. split the data so that it's actually in separate columns not just separated by spaces.
  2. enter the item you're searching for in a cell ($A$1 in the attached sheet)
  3. for every row and every column add a match for example: '''match($A$1, B5:5,0)```. if the column or row contains the search term the match formula will return it's location
  4. simply create a formula up top that returns the max of the match columns and rows

Here's a working example

Upvotes: 1

Related Questions