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.

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.

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.

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.