Index/Match Amateur Question

[Jessica Donadio]

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!

 

[Pete_UK]

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

Pete

 

[RagDyer]

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?

 

[Jessica Donadio]

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

 

[Harlan Grove]

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.

 

[Jessica Donadio]

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.

 

[Harlan Grove]

. . . 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.

 

[Jessica Donadio]

This is precisely what I am aiming to do. I want to base all the proceeding
information on the city I input in row E. However, when I tried the formula,
excel is saying that the cell which the formula references is empty. Are the
placement of the absolute positioning signs important? Should I be including
E230 somewhere in the formula? I should also point out that initially the
record will be on the bottom. But if the company happens to begin with an
"A", it will subsequently be moved towards the top. Sorry, I'm not trying
to be lazy, I tried tweaking it for almost 2 hours last night, and no dice.
Thank you very much for your help.

 

[Spiky]

What I see is a potential Circular problem. To use lookups you need a
separate database. You can't be adding rows in the middle of something
you are already referencing, not easily.

Since you already have a good data list, why not copy your cities/
countries into another sheet and reference that?

 

[Harlan Grove]

This is precisely what I am aiming to do.  I want to base all the proceeding
information on the city I input in row E. . . .

You need to be more careful with terminology. E is a column, not a
row.

Presumably you mean you want to base other columns in the same row on
the city you enter in column E of that row using the information
previously entered.

. . . However, when I tried the formula,
excel is saying that the cell which the formula references is empty. . . .

It could be. If you insert ENTIRE rows, then there'll be no formulas
in any of the cells in those inserted rows in any column.
Alternatively, if you enter a city in column E for which the topmost
preceding entry for that city has no country entered, then Excel would
be correctly returning a blank cell.

. . . Are the placement of the absolute positioning signs important? . .. .

If you mean you didn't enter the formula I provided in my previous
response EXACTLY as I wrote it and/or didn't put it in cell H230, then
why not try to put my formula EXACTLY AS I WROTE IT in cell H230, then
copy H230 up if there are any blank cell in column H above it, then
copy it down as far as needed?

In other words, yes, the formula I provided contained everything I
believe is necessary and nothing superfluous. DON'T ALTER IT.

. . . Should I be including E230 somewhere in the formula? . . .

No.

The formula is meant to work if you insert cells in column E.
Precisely in order to work when cells are inserted, it's NECESSARY to
AVOID referring to INDIVIDUAL cells in column E.

However, I was guessing that you were inserting cells or rows because
you wrote the following: 'when I make a new record and insert the
city'. You probably didn't mean 'insert' in the sense of the Insert >
Cells menu command. You probably meant you were just entering data.

To repeat, you need to be more careful with terminology.

. . . I should also point out that initially the record will be on the bottom.
But if the company happens to begin with an "A", it will subsequently be moved
towards the top. . . .

And now there's company in column A.

At this point, since it seems highly likely there'd be several more
points you haven't mentioned, I'd suggest not worrying at first about
autopopulating anything. Just enter the cells that would contain data
that wouldn't have appeared already in other cells, including city but
not country. Once you have all that entered, sort your table first by
country then by city. If all the country cells are blank, this would
be equivalent to sorting by city only. If the first (topmost) record
were in row 3 (modify as needed based on the topmost row in your
table, since you haven't said I get to use something convenient for
me), then enter the following formula in cell H4 - YES! H4 *NOT* H3.

H4:
=INDEX(H$3:H3,MATCH($E4,$E$3:$E3,0))

Then fill H4 down as far as needed. For example, if your table's last
record was in row 1500, then fill H4 down into H5:H1500. You'll need
to enter the country in cell H3 for the city in E3, but there's no way
around this since there'd be no previous records to which to refer.

These formulas will evaluate to #N/A if you haven't yet entered the
country for the corresponding city, but once you enter the country for
a given city, then if you have automatic calculation enabled all
subsequent rows containing the same city in column E will have the
correct country appear in column H. Any instances of #N/A in column H
mean you have no entry for the city in column E in any previous row.

Once you've entered the countries once for every city, you should have
no more #N/A values appearing in column H. At that point, select the
entire column H range, in my example H3:H1500, and run in succession
Edit > Copy and Edit > Paste Special, Values to convert the remaining
formulas into values.

Then sort the table however you want.