CODEWITHSUNDEEP
excelexcel-formula
Rafael Osipov
Rafael Osipov

Reputation: 740

Excel Match Function When Value Shows Up More Then Once In The Column

I'm trying to Match in Excel value from one worksheet in a list to another column in another worksheet in order to find the first row it shows up and match the result to an Index-Match function. I get an error of #NA even though I entered the right ranges. I believe it is because the value I am trying to match shows up few times, But I need just the first row of those few times.

Any suggestions how can I resolve this?

=INDEX(SAP!$A$1:$BR$15772,MATCH('center'!A2,SAP!$N$1:$N$15772,0),15)

Upvotes: 0

Views: 99

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Based on our discussion, here's what you can do.

Approach 1 Change data type to string like Richard Tompsett has suggested which will be:

=INDEX(SAP!$A$1:$BR$15772,MATCH('center'!A2&"",SAP!$N$1:$N$15772,0),15)

Notice the "&" concatenation after 'center'!A2 which will coerce number to text format.

Approach 2

Following shall also work. It should work for both numeric as well as text types.

=LOOKUP(2,SEARCH(" "'center'!A2&" "," "&SAP!$N$1:$N$15772&" ",1),SAP!$O$1:$O$15772)

Upvotes: 1

Related Questions