Relate name with found function

user_16239103 0
Viewed 50

I have a list of institutions with ID's and I have the same list but not in the same order as this image:

enter image description here

So I don't have it in the same order the next step is that this institutions have different areas and some institutes have some others don't. Like this list:

enter image description here

I have this formula =IF(ISNUMBER(MATCH(C$1,$J2:$O2,0)),"Found","Not Found") that helps me to look for the area in that row but is not in the same order this gives me errors, so I would like to look for the institute name in the range and look for the areas available in that institution row: Here is the image of the error:

You can see in the image that the founds and not founds are not correct

enter image description here

google-sheets-formula google-sheets match if-statement
1 Answers

It can be achieved using a helper table where you first sort your J2:Q14 range using

=SORT(J2:Q14,1,1)

You then use the following in cell C2

=IFERROR(IF(HLOOKUP(C$1,$J17:$Q$29,1,0)=C$1,"Fount",),"NOT")

Drag the cell down and right to fill all other cells.

(Of course the sorted table can be in another sheet altogether)

enter image description here

user_15277800
Related