Is there a way to enter a string, then have the adjacent cell autofill with the cell adjacent to a matching string elsewhere?

Question

I've made a quick example (below) to help explain the question.

https://docs.google.com/spreadsheets/d/1Of_zipv4mxiudtmiNroKyj4FdHkx1XfFcNDq1AbVBYc/edit?usp=sharing

This is maybe easiest to explain with an example, so: what I'd like to happen is when, on sheet 1 (Spec) in column 1 a string is entered (e.g. A2, Pride Lands - Pride Rock) then the cell next to it (B2) will be filled with '-'. This is because on another page in this doc (page 2, All) there is a cell which also contains the same string (A8) and the cell to the right of it contains '-'.

Similarly, on sheet 1 I want B7 to contain a '2', because the other sheet has a string which matches A7 and the cell to the left of that is '2'

Basically each sheet contains string a value (or -) pairs, and if I type a string I want to be given the corresponding value

I've been trying some combos of IF, VLOOKUP, and INDIRECT, but I can't get this to do quite what I want. I believe ADDRESS may also be useful for getting the cell to the right?

It seems this would be easier if all the data on All was contained to 1 column of strings and 1 column of values, but that's not an option unfortunately.

Answer 1

One workaround you could have is to convert the range into a column (with TOCOL), and do the search with XLOOKUP. Then provide the range one column to the right, also converted into a column. For example, if you provide as input TOCOL(All!A:H) then the output should be TOCOL(All!B:I)

=XLOOKUP(A2,TOCOL(All!A:H),TOCOL(All!B:I))