Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a value in a multi-column table
Hi,
I have a spreadsheet that I have to return a specific value based on two criteria, a state and loan amount. My columns are loan amounts while my rows are states. To complicate the situation, i have several companies to choose from in between a range of loan amounts. For example, my loan amount range is 0-250,000 but I have to pick the highest number from company A, B, C. (by the way, the loan amounts are in different cells, Like C3 and D3) So my spreadsheet looks some what similar: 0.00-250,000 A B C D AK 50 60 10 Q AL 10 Q 80 70 AR Q 50 60 0 So I have my state as AK, I need to return 60. Would this complicate the situation with having the Q's also in the table? I tried to combine both an Hlookup and a Match forumla however I could not make it work. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a value in a multi-column table
You were headed in the right direction with the hlookup and match. I used
vlookup and match with A9 as the State and A8 as the Column reference. I don't think it's all you're asking (explain more about what else you want), but let me know what you think about this first round and we'll work out the next step. =VLOOKUP(A9,A1:$E$5,MATCH(A8,$A$1:$E$1),FALSE) -- -SA "cardosol" wrote: Hi, I have a spreadsheet that I have to return a specific value based on two criteria, a state and loan amount. My columns are loan amounts while my rows are states. To complicate the situation, i have several companies to choose from in between a range of loan amounts. For example, my loan amount range is 0-250,000 but I have to pick the highest number from company A, B, C. (by the way, the loan amounts are in different cells, Like C3 and D3) So my spreadsheet looks some what similar: 0.00-250,000 A B C D AK 50 60 10 Q AL 10 Q 80 70 AR Q 50 60 0 So I have my state as AK, I need to return 60. Would this complicate the situation with having the Q's also in the table? I tried to combine both an Hlookup and a Match forumla however I could not make it work. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a value in a multi-column table
From the column values, I have the amounts (0-250k, 251k-500k, etc)merged
across columns C:G and below that are the different companies, company A, B and C. I need to return the highest value per loan amount and state. "StumpedAgain" wrote: You were headed in the right direction with the hlookup and match. I used vlookup and match with A9 as the State and A8 as the Column reference. I don't think it's all you're asking (explain more about what else you want), but let me know what you think about this first round and we'll work out the next step. =VLOOKUP(A9,A1:$E$5,MATCH(A8,$A$1:$E$1),FALSE) -- -SA "cardosol" wrote: Hi, I have a spreadsheet that I have to return a specific value based on two criteria, a state and loan amount. My columns are loan amounts while my rows are states. To complicate the situation, i have several companies to choose from in between a range of loan amounts. For example, my loan amount range is 0-250,000 but I have to pick the highest number from company A, B, C. (by the way, the loan amounts are in different cells, Like C3 and D3) So my spreadsheet looks some what similar: 0.00-250,000 A B C D AK 50 60 10 Q AL 10 Q 80 70 AR Q 50 60 0 So I have my state as AK, I need to return 60. Would this complicate the situation with having the Q's also in the table? I tried to combine both an Hlookup and a Match forumla however I could not make it work. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a value in a multi-column table
OK now I'm confused. How do you have three companies and 5 loan ranges?
Where are the multiple values (of which you want the max) coming from? You're looking to input state and loan ammount right? I just need a clearer picture in my head to move forward. Thanks for the clarification. -- -SA "cardosol" wrote: From the column values, I have the amounts (0-250k, 251k-500k, etc)merged across columns C:G and below that are the different companies, company A, B and C. I need to return the highest value per loan amount and state. "StumpedAgain" wrote: You were headed in the right direction with the hlookup and match. I used vlookup and match with A9 as the State and A8 as the Column reference. I don't think it's all you're asking (explain more about what else you want), but let me know what you think about this first round and we'll work out the next step. =VLOOKUP(A9,A1:$E$5,MATCH(A8,$A$1:$E$1),FALSE) -- -SA "cardosol" wrote: Hi, I have a spreadsheet that I have to return a specific value based on two criteria, a state and loan amount. My columns are loan amounts while my rows are states. To complicate the situation, i have several companies to choose from in between a range of loan amounts. For example, my loan amount range is 0-250,000 but I have to pick the highest number from company A, B, C. (by the way, the loan amounts are in different cells, Like C3 and D3) So my spreadsheet looks some what similar: 0.00-250,000 A B C D AK 50 60 10 Q AL 10 Q 80 70 AR Q 50 60 0 So I have my state as AK, I need to return 60. Would this complicate the situation with having the Q's also in the table? I tried to combine both an Hlookup and a Match forumla however I could not make it work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting a multi-column table in single columns | Excel Discussion (Misc queries) | |||
Finding row & column given value in a Table | Excel Worksheet Functions | |||
Finding a Value in First Column of Table with VLOOKUP? | Excel Discussion (Misc queries) | |||
Creating single lines of data from a multi-column table | Excel Discussion (Misc queries) | |||
Finding intersection of row and column (both variable) in table | Excel Worksheet Functions |