Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

SvK: Feb 20, 2010

ZerodotJander posted:

You probably want to use VLOOKUP.

The syntax is =VLOOKUP(lookup_value, target_range, target_column, FALSE)

Your lookup table has to be arranged so that your index (Zip) is in the leftmost column. So for example, if you have a table like this
code:
Zip       City       State
12345      A          Z
12346      B          Y
12347      C          X
12348      D          W
12349      E          V
Located in cells A4:C9, you could do

=VLOOKUP(G2, $A$4:$C$9, 2, FALSE)

to look up the value in cell G2 in range A4:C9 (dollar signs added so you can fill down without changing the range), find the first occurence of that value (FALSE tells it to look for an exact match) and return the value in the 2nd column (City).

Be careful on how many cells use VLOOKUP if you have several of them based off one another on a few hundred thousand rows of data it can really slow down processing time. If you notice a slowdown and you do not need the data to be 'refreshed' from the 'database' than copy & paste special values over the VLOOKUP ranges to save time.

# ¿ Aug 18, 2010 04:21

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 00:52

SvK: Feb 20, 2010

Scaramouche posted:

Thanks, I can see how that would work. I'd been stuck in the 2003 world so long the only thing I could think of would have been tonnes of countifs and named ranges and so on, and just didn't want to go down that road.

How automated do you want the double check to be? Shouldn't be to hard to write a VBA function that can do this.

# ¿ May 31, 2012 01:30

SvK: Feb 20, 2010

Scaramouche posted:

This person is pretty... not smart with Excel (though great at online marketing) so I didn't want to complexify things with allow scripts to run/popups etc. I like the elegance of the pivot as well, it worked out fine.

Good thing you replied quickly I was like 3/4's the way done with a macro to do it. With dynamic arrays and countif's =).

# ¿ May 31, 2012 02:49

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread