Wildcards in the table_array of vlookup

Hello,

I am semi-familiar with using wild cards in VLOOKUP formulas. However this has always been with the ‘lookup_value’ being the item with the wild card.

I am trying to reclassify a large data set where a range of lookup values relate to one classification and to do this I would like to use the wildcard in the in ‘table_array’ as shown below. Or said another way one value in the table array (ED.OPR.ADV.*) will be found by multiple lookup values (ED.OPR.ADV.BFM.SUP and ED.OPR.ADV.DCM.SAL).

However when I set up my VLOOKUP it returns the value #N/A. Is there a way to perform this function or to set-up my data differently to allow this to run. I have countless permutations of ED.OPR.ADV.* in the data set and I would like to classify all as personal costs.

Thanks,
Jon

Excel problem.png

In case that image is not working I have added table to GoogleDocs: https://drive.google.com/file/d/1BCD...ew?usp=sharing

Thanks Punnam,

I have tried that and at first I thought it worked but in that formula the returns do not exactly line up as expected for instance.

ED.OPR.LES.IT1.SAL returns 'Office' when it should return 'Personnel'
ED.OPR.OPS.FAC.000 returns 'Personnel' when it should return 'Office'

Hi Bo_Ry,

Yes I think that has solved it.

It will take me a while still to understand what it is that you have done and how that works but the results are all correct.

Yes sorry E33 was an oversight on my end I was trying to simplify my example to only a few options but missed deleting that code.

Thank you.
Jonathan

Bo_Ry
what exactly the 9 is doing here could you please explain to me.

Punnam

Originally Posted by Punnam

what exactly the 9 is doing here could you please explain to me.

Punnam

Yes I am trying to understand the same.