So if I have cities filling column E (to E1236) and their respective
countries in column H (to H1236) How do I write the function so that in a
new record the country value automatically matches the city I input? I've
tried this, but no dice:

=INDEX(H:H,MATCH(H10,E:E,0))

"Pete_UK" wrote:

Glad you got it sorted in the end. You can used named ranges to avoid
the problems of absolute addresses.

The INDEX/MATCH approach would be something like this:

=INDEX(return_column,MATCH(lookup,sought_column,0) )

to replace:

=VLOOKUP(lookup,table,column,0)

If you are using VLOOKUP the sought item has to be in the left-most
column of the table, but with INDEX/MATCH the sought_column can be to
the right of the return_column.

Hope this helps.

Pete

On Jul 29, 2:36 pm, Jessica Donadio
wrote:
Wow, forget that, right after I typed that I saw below my thread what the $
sign means before the cell. All is good.

I would still be curious about how this could be achieved through
index/match and what would be the advantages to doing so, but otherwise,
thank you all!



"Jessica Donadio" wrote:
Well the only snag is that when I paste the values down the column it
progressively causes the top rows not to be included in the range of data.
However, I still want to retain the relative cell value of the first parameter

As an example I have countries listed in column H and then the corresponding
postal zones in column I (whose values are either 1,2,3, or Italy). Vlookup
works, but if I start a new record 500 rows down, the cell reference for the
top of the range will move likewise, so that it may not find the country
value if there is only one located towards the top of the list. Is there a
way to incorporate both absolute and relative cell referencing in the same
function?

Or, with index and match, how would I go about it to simply return the
respective postal zones for each country? Thanks again!- Hide quoted text -

- Show quoted text -