Using Vlookup and Match with a wildcard

[Peter Davison]

I have the following formula and in cell AD34 is the words "Own Brand Flag"

=IFERROR(VLOOKUP($A35,'[MasterData.xlsm]New Products'!$A$1:$Z$500,MATCH(AD$34,'[MasterData.xlsm]New Products'!$A$1:$T$1,0),FALSE),"")

Because in the New Products sheet the heading may be different e.g Private Brand Flag or Private Brand

I wanted to use a wild card to find it.
I've tried using an asterix before and after the cell reference but just get an error. (e.g. "*"AD$34&"*")

Can't work out what I am doing wrong.
Any help would be appreciated.

Thanks

 

[Alex Blakenburg]

I don't know if I am missing something here but "*Own Brand Flag*" is not going to find "Private Brand Flag" or "Private Brand".
Can you use "*Brand*" or "Private Brand*" ?

Also your first range is A to Z and the second A to T, this could be fine if you are only expecting the heading in A to T.

 

[Peter Davison]

My mistake it is A to Z on both, however the column is in column S so it would have been ok.
It is a tricky one on what to use in the search because I'm not sure what could be used.
Brand is used in its own column so I'll have to think about this one.

I can try and look up a mid part of the cell content and see where that takes me.
Thanks so much for your help.

 

[Alex Blakenburg]

No problem. Hope you can find something that is consistent enough to match on.
PS: You have MS 365 you could try something along the lines of ="*Brand*" and <> "Brand"

 

[Peter Davison]

How would the
="*Brand*" and <> "Brand"
fit into the VLookup below?

=IFERROR(VLOOKUP($A35,'[MasterData.xlsm]New Products'!$A$1:$Z$500,MATCH(AD$34,'[MasterData.xlsm]New Products'!$A$1:$Z$1,0),FALSE),"")

 

[Alex Blakenburg]

Can you rely on the Heading "Brand" always being to the left of the Heading with Private Brand or Private Brand Flag ?

If you can then this might work for you. It looks using the wild card and starts looking from the right to the left.

=IFERROR(
          VLOOKUP($A35,'[MasterData.xlsm]New Products'!$A$1:$Z$500,
                           COLUMN(XLOOKUP("*"&D34&"*",'[MasterData.xlsm]New Products'!$A$1:$AA$1,'[MasterData.xlsm]New Products'!$A$1:$AA$1,"",2,-1))),
     "")

There are most likely better ways of doing this. I think the question is different enough to the original question for you to open a new thread so that you get other people answering you, who have more experience with linking to other workbooks.
If you do open a new thread then make sure you clarify whether you need it to work when the workbook MasterData is closed. Not all formulas work when the linked workbook is closed.

 

[Peter Davison]

Thankyou for your help, I will give this a try and see where we go with it.
You have been most helpful.
Regards

 

[Alex Blakenburg]

Thanks for the feedback. Happy to help.