• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Cross reference through lookup formula


Please refer to the attached file: Tab "A" has the needed data and Tab "B" should referenece data from Tab "A"
Cell C3 needs to map C2 &A3 through tab A and let me know if they are connected or not.
Tab A has 55 individual stores and each of these stores has 10 assigned stores to them. The yellow section in tab B is what needs to be populated through a formula.

  • 2
1 Solution
barry houdiniCommented:
Try this formula in C3 to give you those results

=IFERROR(ADDRESS(MATCH(1,INDEX((A!$B$1:$B$1000=$A3)*(A!$A$1:$A$1000=C$2),0),0),2),"not connected")

copy across and down as far as required

regards, barry
LadkissonAuthor Commented:
Great! It works but could you modify this formula please? ...it should say "connected" instead of referencing the actual cell in Tab A:

156            not connected
168            $B$9
Harry LeeCommented:
Use this formula instead

=IF(ISERROR(ADDRESS(MATCH(1,INDEX((A!$B$1:$B$1000=$A3)*(A!$A$1:$A$1000=C$2),0),0),2)),"Not Connected","Connected")
barry houdiniCommented:
OK, I thought you wanted the location as shown, if not then for just "connected" or "not connected" try this version

=IF(COUNTIFS(A!$B$1:$B$1000,$A3,A!$A$1:$A$1000,C$2),"connected","not connected")

regards, barry

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now