## Forum Discussion

# Returning a cell's value based on the location of another cell.

I want to search an entire worksheet for a value and once that value is identified, I want to pull the cell's value that exists five cells below it. vlook, xlook, and hlookups don't work because the data isn't organized into a table. Getting confused by the LOOKUP, MATCH formulas.

MDLeach What version of Excel are you using? If you have

**Excel for MS365**or**Excel for the web**, you could try the following:`=TAKE(TOCOL(IFS(range = lookup_value, OFFSET(range, 5, 0)), 2), 1)`

For example:

`=TAKE(TOCOL(IFS(A4:J10000 = B1, OFFSET(A4:J10000, 5, 0)), 2), 1)`

- djclementsBronze Contributor
MDLeach What version of Excel are you using? If you have

**Excel for MS365**or**Excel for the web**, you could try the following:`=TAKE(TOCOL(IFS(range = lookup_value, OFFSET(range, 5, 0)), 2), 1)`

For example:

`=TAKE(TOCOL(IFS(A4:J10000 = B1, OFFSET(A4:J10000, 5, 0)), 2), 1)`

- OliverScheurichGold Contributor
In the attached file i've cobbled together an example which could represent what you are looking for. However it's for a small database because searching an entire worksheet would mean searching more than 17,1 billion cells.

- MDLeachCopper ContributorThank you, Oliver. Let me take a look closer.

- dscheikeyBronze Contributor
Almost the same as Oliver:

`=LET(a,Sheet1!A1:D1000,b,1,c,MMULT(--(a=b),SEQUENCE(COLUMNS(a))),OFFSET(a,XMATCH(0.1,c,1)+4,XLOOKUP(0.1,c,c,"",1)-1,1,1))`

This formula searches in Sheet1 A1:D1000 for the number 1 (stands after 'b' and can be referred to another cell) and outputs the content of the cell 5 cells below.

- MDLeachCopper ContributorThank you, dscheikey. Let me take a look closer.

- mickhenceCopper ContributorTo return a cell's value based on the location of another cell in Excel, you can use the INDEX and MATCH functions. For example, if you want to get the value from the cell in column B that matches the row of the cell containing "target_value" in column A, use:

excel

Copy code

=INDEX(B:B, MATCH("target_value", A:A, 0))

This formula searches for "target_value" in column A and returns the corresponding value from column B.