Sandra Smith
asked on
Lookup a value based on first two columns rather than one
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.
Could you post a sample sheet? Without any confidential information, just for us to provide a complete formula for your question...
ASKER
Just a little sample
SampleData.xls
SampleData.xls
ASKER
End of day, will return tomorrow so no hurry right now, thank you.
What do you want to get? Can you give an example based on your file?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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!
ASKER
I just cannot get this to work
=INDEX(RATING,MATCH(1,IF(D ataSource! A1:A92=J1, IF(DataSou rce!B1:B92 ="AAA or above",1)),0,+DataSource!K 1))
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.
=INDEX(RATING,MATCH(1,IF(D
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.
ASKER
I have changed it to this
=INDEX(DataSource!C:C,MATC H("AAA or Above",IF(DataSource!A:A=J 1,DataSour ce!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.
=INDEX(DataSource!C:C,MATC
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.
ASKER
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:A1 00=J1,Data Source!B3: B100),0))
I am going to keep testing.
=INDEX(DataSource!C3:C100,
I am going to keep testing.
ASKER
It was a struggle, but finally got it to work, don't know why or how, but it does! Thank you.
http://excel-user.blogspot.com/2009/10/sumproduct-sum-values-based-on.html
jppinto