Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
703 views
in Technique[技术] by (71.8m points)

Any way to script excel to run a fancy search for multiple queries at once?

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

enter image description here

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.

enter image description here

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...