Lookup Function Not in Ascending Order

Well, with this formula you are trying to match H10 with column E, and
if it finds a match then get the corresponding entry from column H.
Are you sure you are putting the new entry into H10? Wouldn't it
belong in E10?

I'm not really sure what you are trying to do - could you elaborate a
bit further?

Pete

On Aug 1, 6:53*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))

Well. I had succeeded with Vlookup, but then realized when I add a new
record and it is reordered, the values will not be shown because the data is
no longer above the record. So I was trying to add a new record near the top
of the table (Row 10) to verify that my formula will work.

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 I'm a
bit unclear with this

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

=Index(H:H,MATCH(E?,E:E,0)) does not seem to be correct. Thank you for all
your help, this will really help me out at work, but I don't always have a
lot of time to dedicate to experiment.

Jessica

"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))

If I am to input "Milan" into E?, I want H? to be pre-populated. But I'm a
bit unclear with this

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

=Index(H:H,MATCH(E?,E:E,0)) does not seem to be correct. Thank you for all
your help, this will really help me out at work, but I don't always have a
lot of time to dedicate to experiment.



Well, if I'm reading you right and this formula goes in the cells in
H, it won't work easily since that is potentially circular or at least
messy. You'd have to have all your locations actually typed into the
first, say, 100 rows, then subsequent rows could access those first
100 with whatever lookup will work. And if you come up with a new city
sometime, no lookup will work at all since it is the first time you
typed it in, there's nothing to look up. You might want to have the
city/country list somewhere else to reference. Perhaps put the
location data far off to the right in BA:BB. Then your original idea
of a VLOOKUP would work.

But regardless, on your INDEX/MATCH question, change the INDEX range
to the whole range and put in a column reference. So this checks
column E, then returns an answer from column H, which is the 4th
column of the range:
=INDEX(E:H,MATCH(E10,E:E,0),4)

Precisely what I was looking for. Thank you so much! I am extremely happy
now; No more repetitive data entry!

But regardless, on your INDEX/MATCH question, change the INDEX range
to the whole range and put in a column reference. So this checks
column E, then returns an answer from column H, which is the 4th
column of the range:
=INDEX(E:H,MATCH(E10,E:E,0),4)

Actually, I spoke a little too soon. =INDEX(E:H,MATCH(E10,E:E,0),4)
returns the correct answer if it is already listed above, but not if it is
below...How can I get it to return an answer whether it's listed above or
below? Thank you again

"Spiky" wrote:

If I am to input "Milan" into E?, I want H? to be pre-populated. But I'm a
bit unclear with this

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

=Index(H:H,MATCH(E?,E:E,0)) does not seem to be correct. Thank you for all
your help, this will really help me out at work, but I don't always have a
lot of time to dedicate to experiment.



Well, if I'm reading you right and this formula goes in the cells in
H, it won't work easily since that is potentially circular or at least
messy. You'd have to have all your locations actually typed into the
first, say, 100 rows, then subsequent rows could access those first
100 with whatever lookup will work. And if you come up with a new city
sometime, no lookup will work at all since it is the first time you
typed it in, there's nothing to look up. You might want to have the
city/country list somewhere else to reference. Perhaps put the
location data far off to the right in BA:BB. Then your original idea
of a VLOOKUP would work.

But regardless, on your INDEX/MATCH question, change the INDEX range
to the whole range and put in a column reference. So this checks
column E, then returns an answer from column H, which is the 4th
column of the range:
=INDEX(E:H,MATCH(E10,E:E,0),4)