Under your nose: Excel, VLOOKUP and Columns

Sometimes searching can be tough, when what you’re looking for is trapped inside the language fundamental to the thing on you are searching about. It’s true, sometimes you can’t see the forest for the trees.

A mental block I have with Excel is trying to remember what number a column translates to when your spreadsheet gets really, really wide. Like in the A->CG range. I know that A is 1, and AA is 27…after that things get sketchy.

Who cares? You need this info when you try and use an offset in a vlookup calculation:

=vlookup(some_cell,up_left$corner$:bot_right$corner$,OFFSET,false)

My initial fallback – the Google – would be to search something like:

excel column formula

which of course gives totally useless results – because the search terms themselves are fundamental to Excel: they are the atomic building blocks of Excel. I’d may as well have searched “excel, row, column”.

I’ve run into similar problems trying to search historical info on IP address allocation. Since every node connected to the internet has an IP address, so there’s alot of meaningless hits. I want info on the concept, not the data.

The whole point of this post was for me to document the solution to my column OFFSET problem, so that when I forget it next, I can Google it, and at least hit this page.

Solution 1: The Excel Column Number Formula:

=column()

Solution 2 The Table:

It’s like a formula, but for lazy people:

Column Heading
A- B- C- D-
A 1 27 53 79 105
B 2 28 54 80 106
C 3 29 55 81 107
D 4 30 56 82 108
E 5 31 57 83 109
F 6 32 58 84 110
G 7 33 59 85 111
H 8 34 60 86 112
I 9 35 61 87 113
J 10 36 62 88 114
K 11 37 63 89 115
L 12 38 64 90 116
M 13 39 65 91 117
N 14 40 66 92 118
O 15 41 67 93 119
P 16 42 68 94 120
Q 17 43 69 95 121
R 18 44 70 96 122
S 19 45 71 97 123
T 20 46 72 98 124
U 21 47 73 99 125
V 22 48 74 100 126
W 23 49 75 101 127
X 24 50 76 102 128
Y 25 51 77 103 129
Z 26 52 78 104 130

30 Responses to “Under your nose: Excel, VLOOKUP and Columns”

  1. Very handy. Actually, I’d never heard of VLOOKUP. I have, however written code in the R language to label columns of a data frame using the Excel column labeling scheme. I typically export from Excel into CSV (comma-separated value) format, and then read it into R. But it’s handy to have Excel-style column names when I need to compare with the original spreadsheet.

  2. CLYDE says:

    Cialis UK

    Buygeneric meds…

  3. SERGIO says:

    aloe@vera.gel.indicatii” rel=”nofollow”>…

    Buygeneric drugs…

  4. ENRIQUE says:

    Aricept@official.site” rel=”nofollow”>.

    Buynow…

  5. JORDAN says:

    < a href="http://my.gardenguides.com/members/buy-Cialis? buy@Cialis.now

    Buydrugs without prescription…

  6. RON says:

    cheap@Viagra.now” rel=”nofollow”>…

    Buygeneric pills…

  7. AUSTIN says:

    is overdosing on seroquel painful

    Buydrugs without prescription…

  8. DONNIE says:

    prozac breast feeding

    Buydrugs without prescription…

  9. LLOYD says:

    risperdal@warnings.for.elderly” rel=”nofollow”>…

    Buynow it…

  10. DARREN says:

    Prozac@Prozac.Prozac” rel=”nofollow”>.

    Buygeneric drugs…

  11. OLIVER says:

    coral@calcium.ultra” rel=”nofollow”>…

    Buygeneric drugs…

  12. JAIME says:

    buy@human.growth.hormone.product” rel=”nofollow”>…

    Buygeneric drugs…

  13. DUSTIN says:

    early@childhood.purim.crafts” rel=”nofollow”>.

    Buygeneric drugs…

  14. JORDAN says:

    .

    Buywithout prescription…

  15. JULIAN says:

    can@you.take.vicodin.with.prednisolone” rel=”nofollow”>.

    Buynow…

  16. FRANKLIN says:

    effexor@drug.interaction” rel=”nofollow”>.

    Buygeneric pills…

  17. LEE says:

    is@prometrium.and.blood.clots” rel=”nofollow”>..

    Buywithout prescription…

  18. SHAUN says:

    cutting@proscar.tablets” rel=”nofollow”>.

    Buygeneric pills…

  19. FRED says:

    neurontin@dyskinesia.tardive.dystonia” rel=”nofollow”>.

    Buygeneric drugs…

  20. WESLEY says:

    how@well.does.nexium.cure.barretts.esophagus” rel=”nofollow”>.

    Buygeneric drugs…

  21. NORMAN says:

    changing@from.paxil.to.paxil.cr” rel=”nofollow”>.

    Buyno prescription…

  22. JORDAN says:

    plan@b.skateboards.at.target” rel=”nofollow”>.

    Buywithout prescription…

  23. TED says:

    side@effectsof.flovent” rel=”nofollow”>..

    Buywithout prescription…

  24. Alexander7 says:

    buy@generic.LEVITRA” rel=”nofollow”>…

    Need cheap generic LEVITRA?…

  25. 333vv says:

    ….

    buy@cheap.viagra.online” rel=”nofollow”>.…

  26. s668 says:

    ….

    buy@pfizer.viagra” rel=”nofollow”>.…

  27. ALEJANDRO says:


    GENERIC PHARMACY : -==== Thyroid Pills ====-

    Purchase Cheap Generic Drugs Now!…

  28. CORY says:


    BUY CHEAP DRUGS : -==== Anti Convulsants Drugs ====-

    Purchase Quality Generic Drugs Now!…

  29. derek says:

    appreciate@primates.shirtsleeve” rel=”nofollow”>.…

    ñïñ!!…

Leave a Reply