Index & Match to return text value

Question

Having error in my formula.

How do i get it to display matched text output in column referencing on a column?

F11 to display A1 looking up output produce by formulas in rows above it that is not special character "-".

G11 to display B1 looking up output produce by formulas in rows above it that is not special character "-".

H11 to display B6 looking up output produce by formulas in rows above it that is not special character "-".

As shown in the picture below, formula below is my current input to reference column which produces error.

 =INDEX(F5:F10,MATCH(A5:A10,A5:A10,0))

Is actual hyphen. Which is used in current IF formula to display false value if it doesn't matches — Tyler
– Tyler, Commented Jan 17, 2018 at 7:28
You can't pass range as first argument of MATCH, it should be value or single cell reference. Can you include expected results? — Egan Wolf
– Egan Wolf, Commented Jan 17, 2018 at 7:43
Expected Values as in A1,A2,B1,B6,A6 & A7? Then it would have to include another 7 expected values which is not shown above. — Tyler
– Tyler, Commented Jan 17, 2018 at 7:50
H11 to show B6 or B1? I am confused. Anyway INDEX/MATCH may become involved with Array Entry. I would try to use a formula like =LOOKUP(2,1/(F5:F10=$A$5:$A$10),F5:F10) in cell F11 and copy across. — shrivallabha.redij
– shrivallabha.redij, Commented Jan 17, 2018 at 8:25

shrivallabha.redij · Accepted Answer · 2018-01-17 10:06:38Z

1

Based on Edit, you could use a formula like

=LOOKUP(2,1/(F5:F10=$A$5:$A$10),F5:F10)

Copy it across!

If there's going to only one entry and others as dash (-) then following can also be used.

=SUBSTITUTE(CONCATENATE(F5,F6,F7,F8,F9,F10),"-","")

Explanation for LOOKUP:

We simply compare values in formula column (F in this case) with values in Column A by using (F5:F10=$A$5:$A$10) which produces results like TRUE;FALSE;FALSE;FALSE;FALSE

when these results are used to divide 1 then they're coerced to numbers (TRUE=1, FALSE=0) so the resultant array looks like 1,#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!.

We are looking in TRUE result which will be always equal to 1. So we are using a number larger than 1 i.e. 2 as LOOKUP uses binary search. Any large number can be used.

And then outer LOOKUP simply returns the matched item from passed array.

You can precisely see this by using Formula Evaluate option in the ribbon Formulas >> Formula Auditing >> Evaulate Formula.

edited Jan 17, 2018 at 10:06

answered Jan 17, 2018 at 8:29

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Over a year ago

What does 2,1 does in the loopup? Mind explaining?

Collectives™ on Stack Overflow

Index & Match to return text value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related