VLOOKUP /Index Match - Returning a value if conditions are met?

[CrunchData]

Hi guys

I'm looking for a way to get Excel to return a value if a date is matched.

Please see attached.

Any ideas on how to approach?

Thank you in advance

BR
Crunchy

[FDibbins]

I am currently unable to view files, but have you tried using vlookup? It will return text or value if the conditions are met.

If you have tried it, and it didnt work, are your dates real dates or text looking like a date? Test 1 or 2 with =isnumber(cell-ref)...FALSE indicates text and we will need to convert them

[CrunchData]

Dear PMFDibbins,

Thank you for your swift response.

I've tried using VLOOKUP, but I can't get it to return the appropriate value.

I've reattached the file, please let me know if it works now.

Thank you

BR
Crunch

[FDibbins]

As I said above, I am currently unable to view files

Did you try my test to check that you have real dates?
Can you show me a sample of your formula?

[CrunchData]

Dear FDibbins

I've set up the following in a Pivot table. This is the first table:

Spending Income Value
NAME A
1993/12 -12515
1994/05 -526316 10526
1994/11 -1157895
1995/02 -464342 8421
1995/05 -143082 6410
1995/08 -152439 6219
1995/11 -202865 7786
1996/02 -2354640 8421


Which I'm trying to match

Date 1 S&P 500
1993/12 100
1994/01 96.99550042
1994/02 92.55829465
1994/03 93.62555139
1994/04 94.78624078
1994/05 92.24683932
1994/06 95.15168378
1994/07 98.72926326
1994/08 96.075664
1994/09 98.07728675
1994/10 94.20278153
1994/11 95.36139268
1994/12 97.676549
1995/01 101.2001475
1995/02 103.9658659
1995/03 106.8727888
1995/04 110.7535227
1995/05 113.1101964
1995/06 116.7043906
1995/07 116.6670174
1995/08 121.3450699
1995/09 120.7408522
1995/10 125.6971437

So what I'm trying to do is:
If Date Table 1 matches Table 2, then return 100.


This is just a sample, I have significantly more observations and "Names", thus, I'm looking for a way to automatize this calculation.

BR
Crunch

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[AliGW]

In D4 copied down:

=IFNA(VLOOKUP(A4,$J$4:$K$298,2,0),"")

[CrunchData]

Dear AliGW

Thank you! Exactly what I was looking for!!! :-)

BR
Crunch

[CrunchData]

Thank you FDibbins for you reply.

I'll do so next time around, AliGW solved the issue.

Thank you for your time.

BR
Crunch

[AliGW]

You're welcome.

I suggest you do a bit of reading around the basic VLOOKUP, HLOOKUP and INDEX MATCH MATCH combinations that are used a lot. There are loads of tutorials if you Google.

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

[AliGW]

I've marked the thread as solved for you this time. Please do this yourself next time. Thanks.

[CrunchData]

Thank you for your assistance.

I tried, but to no avail.

May I pick your brain for a second?

Is there any way to return a formula: I'm trying to re-index my variables for each customer e.g. if customer name changes, then I need Excel to calculate a new index benchmark for that particular variable. And I need it to do so for all customers. But it needs to still match the dates...

Thank you.

BR
Crunch

[FDibbins]

If your customer name changes, but all other info stays the same for that customer, I think you may be better off adding a new customer record with the new name. Otherwise all liknks to the old customer name will no longer work

[AliGW]

I've just seen this.

This is bad data handling. All customers should have a unique ID that never changes. This will then mean that any personal details that change (name, address, etc.) will not affect any calculations for that customer: the unique ID will be used to identify them and lookup their details in other tables.

This is one of the basic rules of relational database design: unique primary keys. This should be employed in any Excel set-up where you have inter-dependent tables.

The name should NEVER be that unique key - the name might change. The unique ID never will: it is the constant.