YouTubersLoveExcel#46: VLOOKUP & Previous Bracket Trick

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

Hi All,
I need formula to remove a string or text within a bracket. For instance, (abc) to become abc or [abc] to become abc. Thanks in advance.

Greetings!

I'm having an issue with VLookup, i've attempted to search the forums for the solution but to be honest i wouldnt even know how to keyword it.

My issue is that I have a data source, column A has an I.D number ( starting from 1 all the way up to 900), but where some records have been deleted there are now gaps in the I.D numbering (.. 47, 48, 49, 52, 55 etc.).

Now, When i'm trying to use the ID numbers given to me via a seperate report and attempt to use Vlookup to gain the data from the data source it is picking up the data correctly.. until it reaches an ID number that exists on that sheet but not the data source. What happens then is that it uses the previous entries data rather than declaring that cell as blank.

I've attached an example outlining what i mean: the example.xls file is the information with the VLookup information, and the data source.xls is, well, the data source.

Please note that ID number 281 tells me it belongs to Person 130, when the data source tells me Person 130's ID number is 280.


Is there a way for the formula to give back a blank entry if it cannot find a number within the data source?

Hi there!

I've been a user of the information on this forum for a long time now, but I can't find an answer to this particular issue anywhere (maybe I'm just not using the right search terms), so I've finally joined up! Hopefully, one day, I might be in a position to help someone else out on here, but in the meantime, any help you could offer me with this following issue would be very much appreciated!

Anyway, to business - hopefully the title helps point to the crux of the issue. The problem seems to be related to the behaviour of Excel 2003, and I'd be enormously grateful if someone knows of a workaround we could use.

We have the following scenario:

There is a central data source which is updated monthly - this workbook contains a fairly simple table, with the data in the usual 'row = record' format, ie in this case, 'Supplier A' is on row 11, 'Supplier B' on row 12, etc etc.

Each supplier's name is in column A as a lookup reference.

The column headers are Balanced Scorecard measure metrics, with 'Measure A' in column D, 'Measure B' in column E, etc.

There are also reports for each supplier, with Supplier A's report using the supplier name to lookup each of the metrics for that particular supplier within the data source workbook.

The links within each supplier report are not set to update automatically - each report contains a macro that archives the previous month's data as values before updating the lookup links.

The issue we are experiencing occurs when someone opens two supplier reports concurrently. Here are the steps we've used to replicate:

1: The report for 'Supplier A' is opened and updated with this month's metrics (February), using the macro.

As an example, for Measure A, the current value is '10', with '9' stored against Jan, and '8' against December for this supplier.

This workbook is left open.

2: The report for 'Supplier B' is then opened, whose values were last updated in December.

3: The values in 'Supplier A' report are then checked again, and appear to have reverted to December - for 'Measure A', the report for 'Supplier A' is now showing '8', despite the document having been last updated in February.

Just to clarify, the report for 'Supplier A' isn't now showing values for 'Supplier B' - the report is still looking up from the correct record/row in the data source, but is pulling a value that is now two months out of date! Just to confirm, this December value in the data source has been overwritten twice with new data, once with January's value, then with February's.

We have tried this a number of times, with different workbooks, and it appears to happen consistently, with Excel 'deciding' that the most recently opened workbook is the most current (despite not necessarily being the one most-recently updated).

Is this a known issue? Could anyone suggest a workaround? Does Excel have some way of retaining previous linked/lookup values?

Many, many thanks in advance - there's a lot of head-scratching going on around here at the moment!

Hi, I need to write a macro based on VLOOKUP function.
I need to create a new sheet every week (each sheet is named W1, W2 etc.) and in every new sheet I want the data to be taken from the previous sheet based on a reference number pasted earlier. Between the sheets W1, W2 etc. there is always one extra sheet so actually the data cannot be takien from the previous sheet, but second previous sheet. I hope I was clear enough, Thanks to all for your help!

hello,

i want to vlookup the value in column B of my active sheet on a second sheet then display the data that is in colum N. this is fine becasue it is a normal vlookup.

my problem is that the data in column B and on the lookup sheet comes up more than once in the same column but the data in column N that i want is different.

so basically what i need to do is a vlookup that will go though the list in column B and if it has already found it previously to continue looking for the next one. i guess i will need to use a macro, that is ok if someone can guide me through it, i do not have much experiance.

i hope this makes sense.

thank you very much in advance
Adam

Hi guys,

I have the most unusual problem i just cant find a solution for. I have used Vlookup sucessfully for a while but today I have met my match!!.

I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.

For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.

Any ideas?

Hello all

I'm trying to return two separate date ranges. One is basically last week. ie, last Monday through Last Sunday..

The other is the Last Month, ie. July 1st to July 31st

I'm trying to have this dynamic, so it will calculate these any days...Any suggestions?

Thanks in advance for any input!

Im trying to ad a scroll bare to my work sheet and pust some information in background so i can put othe cell over informaition i have

i want to access the background info by scrolling it over.

I need to show the date of the previous weeks Sunday date and Saturday date. I came across the following formula, and it works well to show me the previous weeks Saturday but not the previous weeks Sunday. any help would be much appreciated! thanks

=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,5,4,3,2,17)

=TODAY()-CHOOSE(WEEKDAY(TODAY()),1,2,3,4,5,6,7)