Hi all,
Is it possible to use Match to find a value within a Named range and then, based on that value, use Match and Index again to find a value two columns across?
I have a list of clients in Column A, with 10 cells between each. In Column B, I have a list of currencies (the same currencies next to each client) and in Column C the rate this client pays for this currency. I want to reference these rates from an external workbook. Is there any way to use Match to locate the client name, then use Index/Match to locate the rate for a particular currency, somehow telling Excel where to look the second time??
Thanks...
Last edited by rabright; 11-13-2008 at 07:36 AM.
Somewhat unclear, but maybe something like this...
=INDEX(C2:C100,MATCH("ClientName",A2:A100,0))
Add the reference to your other workbook and adjust the ranges accordingly.
Hope this helps!
Hi Domenic, thanks for your reply,
Sorry if it wasn't very clear. I have attached a sample of the sheet I am trying to extract the data from.
I'm hoping on the other workbook to be able to have the values (for example) "Client3" and "ILS" and return the value 40, or then "Client2" and "ILS" to return the value 50. I think the way you suggested would return the value 30. This could be in a formula referencing cells where I type in the client name and the currency code, or any other way... I'm just not sure if it is possible.
Also the fact that some cells in column B have multiple entries - I'm aware that there may need to be only one currency code in each cell and that's not a show-stopper if it is the case.
I think your data is better served in more straightforward table format. Here it is laid out that way.
I've named the entire data set (including names and currencies) "Source", and the formula in C11 makes use of that name in the formula to refer to whole table.
Change the name in A11 or the currency in B11 and the formula in C11 looks up the cost in the table above.
Does this help? I thought C11 was what you were aiming for. This can be accomplished on separate sheets, though it is demonstrated for you here on a single sheet.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi JBeaucaire, thanks for the suggestion!
In fact the data was originally set out exactly like this, but because of other workbooks that also pull data from this sheet it needs to be in 3 columns how I've set it out in my example unfortunately.
I do agree that if all that was required was what I'm describing, the way you suggested would be the best but I hope there's another way of doing it that fits the three-column format...
The other sheets can be adjusted, too.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Very true, I guess I shall investigate that possibility...
Don't get me wrong, I'm against unnecessary work. But I often find decisions I've made about a sheet design are causing me issues down the road and I find it's worth the work to put the data in standard formats and learn to use the tools that way to make life easier as I continue to develop. It's just a thought. If it's truly not a win, then leave it all as is and someone will most likely suggest something else.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I know there's a way to do this without them, but I added a helper column and got what you were originally looking for...the ability to select a name and currency and immediately see what they pay for that.
Column A - none of the blanks are blank anymore, they are hidden duplicates of the Client's name. This is necessary for the SUMIF to work.
Column D just looks for the current active currency you've chosen and flags the rows that include that code anywhere in the string.
H6 = multiplies all the found visible values in column E by the names that match F6.
This will work even if you use all 10 with different prices, still good.
Sample attached.
Last edited by JBeaucaire; 11-13-2008 at 08:41 AM. Reason: Removed one helper column
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
As an example, let E1 contain Client2 and F1 contain ILS, then try the following...
G1:
=MATCH(E1,A1:A40,0)
H1:
=MATCH(9.99999999999999E+307,C1:C40)
I1, confirmed with CONTROL+SHIFT+ENTER:
=MIN(IF(ROW(A1:A40)-ROW(A1)+1>G1,IF(C1:C40="",ROW(A1:A40)-ROW(A1)+1)))
J1:
=VLOOKUP("*"&F1&"*",INDEX(B1:B40,G1):INDEX(C1:C40,IF(N(I1),I1-1,H1)),2,0)
Sample file attached...
Hope this helps!
That's great guys I'll have to take a look at these in more detail to see how it works, but looks like one should do the trick... thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks