Excel 2007 : Lookup different tables

Hi,

I really need some help. I need to use a look up function twice. i need to find the postcode area when the postcode is entered. (i have listed the postcodes and areas on the postcode tab, the postcodes in red are the exceptions and have different codes).

I then need to look up the correct rate for the customer from the postcode area and age (on the rates page)

any help?? i am really confused! I have attached the spreadsheet for you as i dont think my explination is very good!

thanks
clare

Try in B9:

=VLOOKUP(B3,postcodes!A:B,2,0)

and in B10:

=INDEX(rates!$B$3:$H$7,MATCH(B8+1&"*",rates!$A$3:$A$7),MATCH(B9,rates!$B$2:$H$2))

Note: if you want a 0 or error if age below 25 or over 69 is entered then

=IF(OR(B8<25,B8>69),"error",INDEX(rates!$B$3:$H$7,MATCH(B8+1&"*",rates!$A$3:$A$7),MATCH(B9,rates!$B$2:$H$2)))

or

=IF(OR(B8<25,B8>69),0,INDEX(rates!$B$3:$H$7,MATCH(B8+1&"*",rates!$A$3:$A$7),MATCH(B9,rates!$B$2:$H$2)))

See attached.

Try:

=VLOOKUP(LEFT(B3,IF(ISNUMBER(MATCH(LEFT(B3,4),postcodes!A:A,0)),4,IF(ISNUMBER(MATCH(LEFT(B3,3),postcodes!A:A,0)),3,2))),postcodes!A:B,2,0)