Any way to script excel to run a fancy search for

Question

This should be a simple task but I'm new to excel and not sure how to run a fancy search.

I'm just trying to view the results of this search, I don't need it to make changes to the cells on the bases of this search.

I'm using control + F to open the search box.

It only allows me to type in one single string, such as "Johnson". Problem is, it finds many rows that contain the name Johnson.

So I'd like to further add a number, such as 1.3338, which it can look for in any cell that's on the same ROW where it found Johnson.

It would then return, for example, Row number 81, which has a cell that contains Johnson and another cell that contains 1.3338.

Is there a way to do this? Perhaps by writing some kind of script for Excel?Thanks!

Answer 1

No need for a script. Assuming the names are in A and the values are in B, you can set-up a search area in another part of the sheet like so:

Formulas:
F3: =IFERROR(MATCH(F1,IF(B1:B30=F2,A1:A30),0),"No match.")
F4: =HYPERLINK("#A"&F3,"Go to row.")

The formula in F3 is an array formula, so you should input it using Ctrl-Shift-Enter instead of just Enter.

The cell in F4 will turn into a hyperlink and will allow you to go to the cell immediately.

Other alternatives are VBA or filters. However, I believe this suffices if simple returning of row and a linker is all you need.

Let us know if this helps.