?? Some kind of look up function - Help and Thanks A Lot!

[TJehn]

Not sure of correct terms and defiantly a beginner at formula syntax, but for
the Guru’s here, this should not be such a hard nut to crack.

I have a table (with static data, which changes very infrequently) to the
right of my working table. A simple example would be something like
AA AB AC
1 3Bedroom 4Bedroom
2 City1 $1200 $1400
3 City2 $1300 $1500


Now to the left side of my worksheet is my working data area (dynamic, as in
it changes a bit frequently).

.. A B C D
1 Address City Bedrooms Rents
2 123 Any St City2 4 [????]
3 456 Other City2 3 [????]
4 789 Some City1 4 [????]

I am looking for a function to look up the Value of B2 and C2 and then
compare it to the above mentioned (static) table, determine if it is City1 or
City2, then determine if it is a 3Bedroom or 4Bedroom then return the
appropriate rental “Value†to the cell in column D.
For 123 Any St the needed formula should see the current value in B2, is
City2, Look to the “Table†and match it to a value in column AA. The formula
should continue to see the current value in C2 and match it to a value in row
3 of the “Tableâ€. These two should intersect at AC3. And I would like that
value (1500) returned to D2.

HLookup and VLookup did not seem to point me to the right directions.
I do not know which functions I am looking for otherwise I might be able to
slog my way through it. Any direction would be helpful.

If I totally misunderstood how to set up the static table or if there is a
simpler solution, by all means, please let me know.

Thanks a lot guys and gals. I have got some great OS/networking skills, but
this is defiantly out of my league and I have posted here before and received
some Spot-On answers.
THANKS!

 

[Bob Phillips]

=INDEX($AA$1:$AC$3,MATCH($B2,$AA$1:$AA$3,0),MATCH($C2&"Bedroom",$AA$1:$AC$1,0))

adjust the ranges to suit

 

[TJehn]

I have reexamined the answer and I am still unable to get this to work.
I have a simple table with a city and address, List Price, Bedroom, Bath and
Square footage.
This table has several entries updated frequently.
Conceder the table to begin at A1.

I have another table that lists market rents.
This table for the most part is static.
Conceder the table to begin at A10.

I need a formula in cell “A7†to look up the city in A column and the number
of bedrooms in the D column and match it in the “Market Rents†table to
return the annual rents (A7).

Thanks for looking at this again


City Address List Price Br. Ba SF
City1 21 Woodridge Pl City1, CA 94101-4150 $254,900 3 2 1519

Annual Rents 1 2 3 4 5
City1 $11,901 $12,539 $17,582 $21,654 $24,905
City2 $12,020 $13,792 $19,343 $23,825 $27,400