Solved

Lookup a value based on first two columns rather than one

Posted on 2011-03-22
13
480 Views
Last Modified: 2012-05-11
I have a set of data that comes in and the first two columns are type of data and the client.  So, the first column would be all "RATING", but the second column would have two clients and then the next column holds the actual value that is being looked up for that client with the next four columns holding the data.  Is there a way to tell VBA to find, wihtin the data of column B, the client, then look for the lookup value and then return the vlaue from the needed column?  I think an Index Match, but am not sure how to apply without having to have a range. DataSample
0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
13 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35194369
For this you can use the SUMPRODUCT() function. Please take a look at this:

http://excel-user.blogspot.com/2009/10/sumproduct-sum-values-based-on.html

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35194383
Could you post a sample sheet? Without any confidential information, just for us to provide a complete formula for your question...
0
 

Author Comment

by:ssmith94015
ID: 35194478
Just a little sample
SampleData.xls
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ssmith94015
ID: 35194491
End of day, will return tomorrow so no hurry right now, thank you.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35194492
What do you want to get? Can you give an example based on your file?
0
 

Author Comment

by:ssmith94015
ID: 35195320
Say, for code "ABCDE" I want the lookup to return CASH value from column 2.  This lookup would be, of course, on the worrksheet for ABCDE client, then for client MNOPEQ, I want their CASH value from coluimn 2 returned.  What I have are large datasets in an Excel workbook for all the clients in one worksheet - this is a master data dump from out intranet.  Then in this same workbook, I have separate worksheets for each of the clients details for reporting to them.
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 500 total points
ID: 35196883
Hi,

Try

=INDEX($D$3:$G$26,MATCH(1,IF($B$3:$B$26=I3,IF($C$3:$C$26=$I4,1)),0),J$2)

where

I3: Client
I4: Fund/ Sector
J2: Column of interest. e.g. 1 or 2 etc

It's an array formula. Confirm with CTRL + SHIFT + ENTER

Kris



0
 

Author Comment

by:ssmith94015
ID: 35198120
Kris, I thought it would have something to do with Index/Match capablity.  I will try this and get back to you.  Also, can this formual be set up with VBA?  This is going into a program using EXCEL as teh backend and user's will not be able to touch the data and once I am gone, they are depending on the interface.
0
 

Author Comment

by:ssmith94015
ID: 35201515
Kris, have been trying to get this to work - it is me.  I think part of my problem is I simply do not understand what is going on.  But am still working on it!  
0
 

Author Comment

by:ssmith94015
ID: 35201798
I just cannot get this to work

=INDEX(RATING,MATCH(1,IF(DataSource!A1:A92=J1,IF(DataSource!B1:B92="AAA or above",1)),0,+DataSource!K1))

There was some changes in the underlying columns so they start with ClientCode, Fund/Sector, then the data columns - basically the RATINGS column (A) was deleted.  But I simply cannot get this to work and I don't know what to do.  RATING is the range in the source worksheet (DataSource) and J1 is the value (client code) on the client's worksheet.
0
 

Author Comment

by:ssmith94015
ID: 35202122
I have changed it to this

=INDEX(DataSource!C:C,MATCH("AAA or Above",IF(DataSource!A:A=J1,DataSource!B:B),0))

but still does not work.  Some of the lookups are actually different than the column, so I am testing this way also.  The different client codes are in column A (first variable), Fund/Sector is Column B 9 (which is where the phrase "AAA or Above" would be located-Second variable)  and the value I want is in MarketValue in column C - so simply but I am getting nowwhere.
0
 

Author Comment

by:ssmith94015
ID: 35202140
I do not know why but all of the sudden, this seems to work!

=INDEX(DataSource!C3:C100,MATCH("AAA or Above",IF(DataSource!A3:A100=J1,DataSource!B3:B100),0))

I am going to keep testing.
0
 

Author Closing Comment

by:ssmith94015
ID: 35202171
It was a struggle, but finally got it to work, don't know why or how, but it does!  Thank you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question