Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup Formula
I am not sure if it shoudl be a Vlookup or Hlookup formula and I am not sure
how to procede. I have a 4 x 4 table that looks like this from A1:D4 USD EUR GBP USD 1.0 1.19 1.73 EUR 0.83 1.0 1.45 GBP 0.57 0.68 1.0 In cell G1 I have a cell where I enter USD or EUR or GBP In cell F12 I have a cell where I enter USD or EUR or GBP In cell G12 I enter a number. I need a formula for cell H12 that will multiply the cell in G12 times the correct cell in the pivot table based on G1 and F12. For example if G1=USD and F12=USD then G12 should be multiplied times B2. what I am trying to do is create a simple report that calculates an expense item in one currency to a report base currency. Thanks for any help provided. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup Formula
This formula:
=INDEX(B2:D4,MATCH(F11,A2:A4,0),MATCH(G1,B1:D1,0)) *G11 may be correct, or it's possible F11 and G1 need to be switched. Couldn't tell from data given. -- Kevin Vaughn "Frick" wrote: I am not sure if it shoudl be a Vlookup or Hlookup formula and I am not sure how to procede. I have a 4 x 4 table that looks like this from A1:D4 USD EUR GBP USD 1.0 1.19 1.73 EUR 0.83 1.0 1.45 GBP 0.57 0.68 1.0 In cell G1 I have a cell where I enter USD or EUR or GBP In cell F12 I have a cell where I enter USD or EUR or GBP In cell G12 I enter a number. I need a formula for cell H12 that will multiply the cell in G12 times the correct cell in the pivot table based on G1 and F12. For example if G1=USD and F12=USD then G12 should be multiplied times B2. what I am trying to do is create a simple report that calculates an expense item in one currency to a report base currency. Thanks for any help provided. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup Formula
Use MATCH( ) with the value in G1 to find the relative position in the
column containing USD, EUR, GBP, then use INDEX around that with your F12 value to tell you how far over to go to get the value you need. Examples can be found in Excel's Help. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
formula to lookup table in another Worksheet please | Excel Worksheet Functions | |||
How do I have a formula lookup a sheetname? | Excel Worksheet Functions |