Vlookup or Index/match with roman numbers

[mrichard]

Hi all,

I have tried both vlookup and index/match formulas to pull data and it keeps pulling the first match each time.
Example:

Table:
Network Engineer I
Network Engineer II
Network Engineer III
System Engineer I
System Engineer II
System Engineer III

My formula is -

=VLOOKUP("*"&C37&"*",'Sheet 1 GSA'!$B$2:$L$40,2,FALSE) and also used - =INDEX('Sheet 1 GSA'!C:C,MATCH("*"&C37&"*",'Sheet 1 GSA'!B:B,0)) The data I am looking for is staked in two different cells (in other words I have several labor categories in two different cells - using the alt return to place on top of one another). One cell has 5 lcats and it contains the Network Engineer III . The other cell contains the Network Engineer I and II. Both formulas sort of work but they are pulling the Network Engineer III because its the first that it sees. Anybody have a work around. Thanks in advance Mark

 

[pjmorris]

Mark, you are making life challenging by having multiple data elements in one cell. Using Alt Return is ok if its a note or something similar, but as soon as you want to manipulate the individual data elements you run into this sort of difficult.

That said, it doesn't sound like an impossibility, my first thought would be to try this:

VLOOKUP("*" & C37 & CHAR(10) & "*", Sheet 1 GSA'!$B$2:$L$40, 2, FALSE)

The reason that your code returned the wrong result was because 'Network Engineer I' is actually part of 'Network Engineer III' (at least as far as the computer is concerned. The solution works by looking for the Alt-Return character. However, this will run into problems if you haven't put Alt-Return at the end of list of entries. A tidier solution would be after each entry put a comma before the Alt-Return. The formula above would then become:

VLOOKUP("*" & C37 & ",*", Sheet 1 GSA'!$B$2:$L$40, 2, FALSE)

HTH

 

[mrichard]

Peter,

Bingo! thanks a bunch. Would of never thought to put a comma in there.

Much appreciated
Mark

 

[jasonb75]

It might work this time, but as Peter has already said, you're making things far more complicated than you need to by using bad practice.

The other cell contains the Network Engineer I and II.

Stick to one in each cell and it will make things a lot easier. It would be better to adopt a more normailsed approach now rather than leaving it til later. You will inevitably reach a point where a workaround is not possible and you have to reformat it, at which point you will have to redo all of your workarounds to match the corrected layout.

With a normalised layout, you would have most likely had a vlookup formula working an a couple of minutes, how much extra time have you lost trying to make it work?

 

[pjmorris]

Thanks for the Feedback Mark, much appreciated - but do note Jason's comments also. It'll be worth it.

 

[mrichard]

Thanks Jason, you make a good point.

Thanks all!