CODEWITHSUNDEEP
excelexcel-formula
James
James

Reputation: 499

Lookup Function Excel

I am working on a lookup function and I cant seem to make it work. I am looking up a value from one worksheet into another. The issue I am having is that some names in the excel sheet iI am looking up are not spaced at the same as the other sheet. For example instead of John Davis, the lookup sheet might have the name as JohnDavis. Or Peter Lee Thomas might be Peter LeeThomas. So my looking function is failing because of this. 

=IF(B2="AD Non Chargeable","Internal",INDEX(Sheet3!B:B,MATCH('Raw Data'!B2,Sheet3!A:A,0)))

Can you please advice on the best way around this? My Lookup sheet is Sheet3

Upvotes: 0

Views: 120

Answers (2)

James Hawkins
James Hawkins

Reputation: 218

One solution would be to create another column Sheet3, in this example B, to remove all spaces, like this:

In cell B2 (and copied down): =substitute(A2,"","")

Then alter your lookup to alter its data similarly and to search in this space eliminated row B:

=IF(B2="AD Non Chargeable","Internal",INDEX(Sheet3!B:B,MATCH(substitute('Raw Data'!B2," ",""),Sheet3!A:A,0)))

Upvotes: 0

zipa
zipa

Reputation: 27869

Okay, if for example your data looked like this:

A            B  C           D
Some Text 1  2  SomeText3
Som e Text 2 3  Some Text 2
So meText 3  4  SomeTex t1

Lookup formula in column D would be:

=INDEX($B$1:$B$3,MATCH(SUBSTITUTE(C1," ",""),SUBSTITUTE($A$1:$A$3," ",""),0))

Make sure to apply this formula with Ctrl + Shift + Enter.

The result will look as expected:

A            B  C           D
Some Text 1  2  SomeText3   4
Som e Text 2 3  Some Text 2 3
So meText 3  4  SomeTex t1  2

Upvotes: 1

Related Questions