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)) Thank you for any help!

Why re-post, Jessica? I answered your first query a few minutes ago.

Pete

On Aug 1, 7:14*pm, Jessica Donadio
wrote:
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)) * Thank you for any help!

If your *countries* are in Column H,
why are you using H10 as a search criteria?

Isn't H10 also a country?

Maybe just a typo, and you really mean E10?
OR
some other cell assigned to contain the criteria?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jessica Donadio" wrote in
message ...
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)) Thank you for any help!

It's essentially like this:

E H
Citty Country

Los Angeles USA
New York USA
? ?
Milan Italy
Barcelona Spain

If I am to input "Milan" into E?, I want H? to be pre-populated. But when I
tried this:

=INDEX(E:H,MATCH(E?,E:E,0),4)

only if I input Los Angeles of New York in E? (The values above) will give
me the correct country in H? . How can I fix it so that also the values
below are included?

Jessica

Jessica Donadio wrote...
It's essentially like this:

[typos corrected]
E_____________H
Ci[t]y________Country

Los Angeles___USA
New York______USA
?_____________?
Milan_________Italy
Barcelona_____Spain

If I am to input "Milan" into E?, I want H? to be pre-populated. But when I
tried this:

=INDEX(E:H,MATCH(E?,E:E,0),4)

only if I input Los Angeles o[r] New York in E? (The values above) will give
me the correct country in H? . *How can I fix it so that also the values
below are included?

So there could be duplicate entries in different rows? In other words,
E3 and H3 could be contain Los Angeles and USA, respectively, and so
could E20 and H20, E35 and H35, etc. The column H formulas could
return the correct city only in rows 20 and 35, but not in row 3. If
you enter a city on E200, that city would need to have appeared in a
previous row in order for the country to appear in H200. If E200 were
the first instance of the city in the table, you'd have to enter the
country in cell H200. Excel is COMPLETELY IGNORANT of geography, so
you need to provide the geographical data.

Also, you shouldn't use entire column references. First, they'd cause
circular recalculation. Second, even if there weren't curcular
recalculation, they're inefficient. So, if your city/country entries
begin in E2/H2, there's no reason to put a formula in H2 since you'd
need to enter the country. Enter the following formula in H3 and fill
down as far as needed.

=VLOOKUP(E3,E$2:H2,4,0)

It'll display #N/A whenever the city in the same row in column E
doesn't appear in a previous row in the table. So you'll need to enter
the country whenever column H displays #N/A. No way around that.

Thank you very much for your in-depth response. First. what I wrote above is
a bare bones example. I've already inputted data for over 400 or so cities.
Therefore, what I want now is that when I make a new record and insert the
city, all the rest of the corresponding columns are pre-populated based on
data I've already inputted. VLookup works, but if the new record I made
would be placed near the top subject to the ordering, all the proceeding data
is lost. That is why I was trying to use index/match because I need a
formula that will work regardless of positioning.

Jessica Donadio wrote...
....
. . . I've already inputted data for over 400 or so cities. *
Therefore, what I want now is that when I make a new record and insert the
city, all the rest of the corresponding columns are pre-populated based on
data I've already inputted. . . .
....

Do you mean you could have data already in, say, E2:H420, then insert
a new row 230, then if you enter a city in E230 that already appears
in either E2:E229 or E231:E421, cell H230 would automatically be
filled with the corresponding country? If so, formulas won't work.
You'd need an event handler macro. The reason is that if you insert an
entire row 230, then there's not going to be a formula in column H of
that row, so no formula to evaluate to the country no matter what you
enter in column E in that row. If you only insert a new cell E230, so
leaving the rest of row 230 unchanged, then the formula in H230 will
refer to cell E231, the cell that HAD been in the same row prior to
your insertion of a new cell E230.

Actually, you could refer to row E in the same row at all times using
a formula like this.

H230:
=IF(COUNTIF($E$2:$E229,INDEX($E:$E,ROW(H230))),
INDEX(H$2:H$229,MATCH(INDEX($E:$E,ROW(H230)),$E$2: $E229,0)),
IF(COUNTIF($E231:$E$10000,INDEX($E:$E,ROW(H230))),
INDEX(H$231:H$10000,MATCH(INDEX($E:$E,ROW(H230)),$ E231:$E$10000,0)),
""))

This would allow you to insert new cells just in column E and have
column H update as expected.