CODEWITHSUNDEEP
excelexcel-formulaformulavlookuplookup
Joe
Joe

Reputation: 143

Excel - Find cell, navigate to column to the left, get last non-empty value of that column

I am trying to create a formula that does the following:

1) Finds a cell based on a text value (i.e. C2 contains the domain, "example.com," search for that) from another sheet inside the same workbook, with a range of A1:ZZ1.

2) Once the cell match has been identified, navigate to the column left of the matched cell.

3) Grab the last non-empty value of that column.

I have been using this code to do it manually, but it requires entering the column myself, and doesn't adjust if column are deleted from the sheet.

=LOOKUP(2,1/(Sheet1!A:A<>""),Sheet1!A:A)

Thanks for your time!

Upvotes: 1

Views: 168

Answers (1)

Error 1004
Error 1004

Reputation: 8220

The value i want to search appears in Sheet1 and the searching area is in Sheet2. You could modify the below formula and try:

=LOOKUP(2,1/(INDIRECT("Sheet2!" & SUBSTITUTE(ADDRESS(1,MATCH(C2,Sheet2!1:1,0)-1,4),"1","") & ":" &SUBSTITUTE(ADDRESS(1,MATCH(C2,Sheet2!1:1,0)-1,4),"1","") )<>""),INDIRECT("Sheet2!" & SUBSTITUTE(ADDRESS(1,MATCH(C2,Sheet2!1:1,0)-1,4),"1","") & ":" &SUBSTITUTE(ADDRESS(1,MATCH(C2,Sheet2!1:1,0)-1,4),"1","") ))

Sheet1:

enter image description here

Sheet2:

enter image description here

Upvotes: 1

Related Questions