Get the Cell Address of a Lookup Value in Google Sheets

Are you tired of manually searching for the cell address of a lookup value in Google Sheets? Don’t worry, I’ve got you covered! In this article, I’ll share three different formulas that will make your life easier and save you valuable time. So let’s dive in and discover the secrets to finding cell addresses effortlessly.

Formulas to Get the Cell Address of a Lookup Value in Google Sheets

Before we explore the formulas, let’s take a look at some sample data. Here’s an example dataset that we’ll be using for our demonstrations:

Now, let’s move on to the three formulas that will help us retrieve the cell address of a lookup value in Google Sheets:

Formula 1: CELL Function Based

The first formula combines the Vlookup function with the CELL function. Let’s say we want to find the cell address of the name “Tim” from the “Score 2” column in our dataset. Here’s how the formula looks:

=CELL("address",VLOOKUP("Tim",C2:F7,3,0))

By using Vlookup to return the lookup value instead of the cell ID, we can then use it as a reference within the CELL function. The result will be the cell address of the lookup value, which in this case is “$E$5”.

Formula 2: CELL Function Based (Index-Match)

The second formula follows a similar approach using the Index-Match combination. Here’s the formula to retrieve the cell address of the lookup value “Tim”:

=CELL("address",INDEX(C2:F7,MATCH("Tim",C2:C7,0),3))

Using the Index and Match functions, we can offset the desired number of rows and columns to reach the row that contains our lookup value. Once again, we use the CELL function to obtain the cell address, which in this case is also “$E$5”.

Formula 3: IF and SORTN Based

The third formula takes a different approach by utilizing the logical IF formula along with SORTN. Here’s how you can use this approach step by step:

1. Use the IF formula to return the row number that matches the name “Tim”:

=ARRAYFORMULA(IF(C2:C7="Tim",ROW(C2:C7)))

2. Replace the ArrayFormula with SORTN to obtain only the row number:

=SORTN(IF(C2:C7="Tim",ROW(C2:C7)))

3. Append the column letter “E” (Score 2 column heading) to the output from the previous step:

="$E$"&SORTN(IF(C2:C7="Tim",ROW(C2:C7)))

And there you have it! The third formula will give you the cell address of the lookup value “Tim”. It’s that simple!

Cell Address of Lookup Intersection Value in Google Sheets

What if you want to find the cell address of an intersection value in Google Sheets? No worries, we can handle that too.

Let’s assume we want to lookup and find the cell address of an intersection value using the following criteria:

• Value to Lookup: “Tim”
• Column to Lookup: “Score 1”

To modify the first two formulas accordingly, replace the number three in the formulas with the following horizontal match formula:

MATCH("Score 1",C2:F2,0)

The modified Formula 1 will be:

=CELL("address",VLOOKUP("Tim",C2:F7,MATCH("Score 1",C2:F2,0),0))

And the modified Formula 2 will be:

=CELL("address",INDEX(C2:F7,MATCH("Tim",C2:C7,0),MATCH("Score 2",C2:F2,0)))

That’s it! With these formulas, you can easily retrieve the cell addresses of lookup values and intersection values in Google Sheets. Say goodbye to manual searching and hello to productivity!

If you want to learn more about Google Sheets tips, tricks, and techniques, head over to Crawlan.com. Stay tuned for more exciting articles and let’s make spreadsheet magic together!

Enjoy the journey and happy sheeting!