CODEWITHSUNDEEP
excelindexingmatchformula
Tyler
Tyler

Reputation: 616

Index & Match to return text value

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))

enter image description here

Upvotes: 0

Views: 3754

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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.

Upvotes: 1

Related Questions