VLLOOKUP using two worksheets in the same workbook using wildcards help pls

**lifeswonderful** · 09-19-2021, 08:31 AM

Hi all and thank you for accepting me into this group. This is my first post and I really would like some help.

Before I posted this I have research for many hours into trying to find a way of doing this. And the closest answer I have come across is within this website and after trying to follow the steps I am still stuck -

So here is what I am trying to do - I have attached the EXEL sheet for ref

The First Vlookup code to be put into Cell A3 worksheet called DATA

using Vlookup to look up down a list of data in a column worksheet called "codes" column A

and compare that data in cell C3 workbook "data" and if any part of that string in cell C3 "data" is in any cell in column A worksheet "codes" then I would like the corresponding numeric value in COLOUM B "codes" to be placed into A3 worksheet "DATA"

I have tried this but it does not work

=VLOOKUP("*"&C3&"*",CODES!A1:B16,2,0)

so for example with the correct vlookup code cell A3 workbook "data" should have the value 3010

Thank you

**AliGW** · 09-19-2021, 08:55 AM

Welcome to the forum.

Please save the file as an .xlsx file (as you are using Excel 2013) and attach again. .xlsis a very outdated format. Thanks.

In A3 copied down:

=IFERROR(LOOKUP(1000,SEARCH(CODES!$A$1:$A$16,C3),CODES!$B$1:$B$16),"")

**lifeswonderful** · 09-19-2021, 01:58 PM

Hi

Thanks for your assistance. I am using office 2003 and it appears the code does not pull out the data

all I get is "#NAME?" in cell A3

could you please have one more look at it for me

Thank you

**TMS** · 09-19-2021, 02:46 PM

Your profile says 2013.

Try:

Formula:

Please Login or Register  to view this content.

I think IFERROR was introduced in Excel 2007.

If the formula still doesn’t work pressing Enter, try committing it with Ctrl-Shift-Enter

**lifeswonderful** · 09-19-2021, 03:01 PM

Hi Trevor,

Thanks for the help but unfortunately this did not work, getting this "#VALUE!"

Thanks

-Peter

**TMS** · 09-19-2021, 03:04 PM

As I said, if the formula still doesn’t work pressing Enter, try committing it with Ctrl-Shift-Enter. The cursor must be in the formula bar once you have typed it or copied it. That is, you are still in editing mode.

**lifeswonderful** · 09-19-2021, 03:23 PM

Hi

Tried this with Ctrl-Shift-Enter while the cursor is in the formula bar

pls look at the screenshot and tell me if this is correct - many thanks

**CARACALLA** · 09-19-2021, 03:30 PM

DATA

A3=IF(DATA!$C3<>"",INDEX(CODES!$B$1:$B$16,MATCH("*"&CODES!$A1&"*",DATA!$C$3:$C$19,0)),"")

Copy down

**TMS** · 09-19-2021, 03:44 PM

The formula I posted uses IFNA not IFERROR. That didn't exist in Excel 2003.

**lifeswonderful** · 09-19-2021, 04:03 PM

Hi TMS

Sorry, new to this and missed your formula in your reply - just tried this and used the keys Ctrl-Shift-Enter while the cursor is in the formula bar

This is what happens

Many Thanks

**lifeswonderful** · 09-19-2021, 04:09 PM

Dear Caracalla

Thank you for your formula

This seems to work for me - great!

What would be great is if you

would you be able to help me understand this formula - the steps - and functions - steps that this line of code ( each section at a time ) does to get me the results? If I ever need to tweak it at least I understand it and I would be able to make my own adjustments

Once again thank you

**Pete_UK** · 09-19-2021, 04:21 PM

Try this in A3:

=VLOOKUP(LEFT(C3&" ",FIND(" ",C3&" ")-1),CODES!$A$1:$B$16,2,0)

then copy down. Note that there are 2 spaces between each " ", but the forum software may remove them. You will get some results with #N/A errors, as your data is inconsistent. To show blanks instead you can do this in A3:

=IF(COUNTIF(CODES!A1:A16,LEFT(C3&" ",FIND(" ",C3&" ")-1)),VLOOKUP(LEFT(C3&" ",FIND(" ",C3&" ")-1),CODES!$A$1:$B$16,2,0),"")

and copy this down.

Hope this helps.

Pete

@Trevor:

I don't think IFNA was available in XL2003, though I never had that version - are you thinking of ISNA ?

**TMS** · 09-19-2021, 04:30 PM

@Pete:

I don't think IFNA was available in XL2003

Yes, I think you are right, on both counts.

I did have 2003 but it's such a long time ago. I struggle to recall what functions are in which versions. It's even worse now using Excel 365 because I don't have to worry about Array Formulae so testing formulae can be confusing.

**TMS** · 09-19-2021, 04:58 PM

Ah, it's all coming back to me …

Formula:

Please Login or Register  to view this content.

**lifeswonderful** · 09-21-2021, 04:54 AM

Thank you - works

**lifeswonderful** · 09-21-2021, 04:54 AM

Thank you - this also works

**AliGW** · 09-21-2021, 04:55 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**lifeswonderful** · 09-21-2021, 04:55 AM

Thank you

**lifeswonderful** · 09-21-2021, 11:08 AM

Hi - Just tesing the formula's submitted with a real spreadsheet and data and stumbled upon a few more questions and help with the formula. Can you please tell me how to take the "solved" flag off this and ask a few more questions pls

**AliGW** · 09-21-2021, 11:11 AM

The same way you added it, BUT you should only continue here with queries related to THIS exact issue. If these are NEW issues to solve other problems in the spreadsheet, then you should start a new thread (it's ONE thread per issue here). Hope this makes sense.

**lifeswonderful** · 09-22-2021, 03:36 AM

~Hi TMS

And thank you for your answer.

After testing the formula on a real spreadsheet with real data I stumbled across some errors and I would like help in tweaking the formula to get it to work again -

It looks like the formula search only go down to worksheet "CODE" column 16. As this was all I had in the test excel sheet uploaded I understand why.

My real data has many many more lines 200 maybe even 300 codes that I would like to use vlookup code and update it

As I fully do not understand the codes I tried to use logic to change the codes

when I changed the codes

CODES!$B$1:$B$16
CODES!$A$1:$A$16

to

CODES!$B$1:$B$1000
CODES!$A$1:$A$1000

I get errors

Also

it appears that the answers provide don't allow for any spaces in between columns of "CODES" ( I would have liked to use the spaces to separate the codes to different categories with headings. If you can do your magic I Would really appreciate it thank you so much

I would like to be able to add codes in the future, so I would like the code to be able to go down the full length of A and B in "CODES" without telling to stop at A500 or B500

many thanks

**AliGW** · 09-22-2021, 03:46 AM

There are instructions at the top of the page explaining how to attach your sample workbook.

**lifeswonderful** · 09-22-2021, 04:05 AM

Dear AliGW

This is all new to me - so thank you for your assistance - I have uploaded the new sheet.

As you can see I have now put the headings and spaces in the "CODE" worksheet to show what I am trying to achieve and to show you why the formula is now not working as I would like it to. I now realise I should have done this the first time

Many many thanks

**AliGW** · 09-22-2021, 04:22 AM

This will get you a bit closer:

=IFERROR(LOOKUP(1000,SEARCH(IFERROR(LEFT(C3,FIND(" ",C3)-1),C3),CODES!$A$2:$A$35),CODES!$B$2:$B$35),"")

Change A25 on the CODES sheet to something like WAGES SECTION.

I am looking at the items that still return no match ...

**lifeswonderful** · 09-22-2021, 04:46 AM

That's great - thank you for trying to get this right for me - if you can get it to work that would be magic !!!

**AliGW** · 09-22-2021, 05:00 AM

I've got this far:

=IFERROR(MAX(LOOKUP(1000,SEARCH(C3,CODES!$A$2:$A$35),CODES!$B$2:$B$35),LOOKUP(1000,SEARCH(CODES!$A$2:$A$35,C3),CODES!$B$2:$B$35)),MAX(LOOKUP(1000,SEARCH(LEFT(C3,FIND(" ",C3)-1),CODES!$A$2:$A$35),CODES!$B$2:$B$35),LOOKUP(1000,SEARCH(CODES!$A$2:$A$35,LEFT(C3,FIND(" ",C3)-1)),CODES!$B$2:$B$35)))

I am struggling to work out the anomalies. I'm hoping another helper will join in to help track them down!

It would be best, really, if the format of the codes tab could change.

**AliGW** · 09-22-2021, 05:20 AM

If it were me, I would normalise the lookup data using PowerQuery, then use the query table as the lookup. This could be on a separate, hidden sheet if you wanted.

Have a look at the attached.

M Code:

Please Login or Register  to view this content.

**lifeswonderful** · 09-22-2021, 05:42 AM

Dear ALi

I much prefer and feel more comfortable with your first method. If you can ( and I appreciate the effort you're making ) get it to work I will be using this method and not your suggestion.

Thank you

**AliGW** · 09-22-2021, 05:49 AM

I've already spent over an hour on it this morning. I need to move on to other things - sorry.

I will be using this method and not your suggestion.

Your choice: not a great decision for the long term, in my view. Badly arranged data is always going to be a problem, and any workaround you get now may well fall over in the future. You would be far better off investing a small amount of time in understanding what normalised data looks like and applying those rules, otherwise you will (been there, got the T-shirt) regret it at some point in the future.

**lifeswonderful** · 09-22-2021, 06:27 AM

Dear Ali

Thank you - I understand where your coming from and I agree ( as a newbie I did not think the data was badly arranged )

OK - I would like to try and use your suggested method. Could you please give me instructions on how to implement it please

do I need to place this code

Please Login or Register  to view this content.

in the background somewhere?

=LOOKUP(1000,SEARCH(LookupTable[Type],C3),LookupTable[Value])

is this an array do I need to do an

Shift-ctr - enter ?

Many Thanks

**AliGW** · 09-22-2021, 06:35 AM

I'm sorry - I don't have time at the moment to explain in detail, however the basic steps are (to be followed in a copy of your file that does NOT already contain the PQ):

1. Select the whole lookup range on the CODE tab and, with it selected, click From Table on the Data ribbon. This launches the PQ editor.
2. On the ribbon,select Advanced Editor and replace ALL the code you see there with the code I have given you. Save and close the editor
3. Rename the query from Table1 to LookupTable in the box at the top of the PQ window on the right.
4. Click on Close & Load To ... on the left of the PQ ribbon and select D1 on the CODES tab as the destination.

is this an array do I need to do an

Shift-ctr - enter ?

You should not have to, no, but if it returns an error or the wrong values, it won't hurt to enter it that way.

**AliGW** · 10-14-2021, 06:31 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Re: VLLOOKUP using two worksheets in the same workbook using wildcards help pls

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] SUMIF or SUMPRODUCT on External Workbook w/ Wildcards.

[SOLVED] Copy range from another workbook with wildcards

[SOLVED] Wildcards in workbook name

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions