Using IFERROR(INDEX(SMALL(ROW to search for and return a value while ignoring blanks

Hi everyone,

I am trying to create a page which automatically updates based on information entered elsewhere. The page searches a different sheet for a specific value in Column A (*), and when it finds the value in Column A, it returns Column B as the result. I have a few issues. First is that the character i'm looking for is a *. Second, only a few rows contain the * in an entire search parameters and I can't have blanks in my sheet so I'm trying to make it so it ignores rows without the *.

What I am trying to use right now is something along the lines of this:

=IFERROR(INDEX(Estimate!$A$1:$B$500,SMALL(IF(Estimate!$A$1:$A$500<>"",ROW(Estimate!$A$1:$A$500)-ROW(Estimate!$A$1)+1),ROW(1:1)),2),"")

NOTE: This function doesn't work as intended (returns a blank) and is intended to give an example of what I was thinking.

In the example attached, the goal is, in the "Hyperlinks" sheet, to return the values 100.01,100.1 and 127.1 which are on the "Sheet1" with an * in the column next to them.

I'm not sure if it will be any help but a few weeks back I asked a somewhat similar question which can be found by clicking on my username and going to "Find latest started threads". I would link but I don't have the privileges.

Thanks for your help,
LearningByError

One way:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$1:$A$7)/($A$1:$A$7="*"),ROWS($1:1))),"")

and

=IF(E2<>"","*","")

see sheet.

One way is to run through the formula using Formulas/Evaluate formula, to see each step. It's a bit of a pain as the dialogue box is not re-sizable.

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$1:$A$7)/($A$1:$A$7="*"),ROWS($1:1))),"")

Blue. Return the values from column B
Red. In ascending row order
Orange. Return the row numbers
Cyan. Where the value in column A is an asterisk.
Green, return the smallest, then the next smallest row number (it's simply a counter...)
Black. Otherwise return a blank