Author Topic: Excel vlookup help required  (Read 1194 times)

Excel vlookup help required
« on: 06 December, 2018, 09:20:55 am »
I am attempting to use a vlookup that somebody else has written for the same purpose but on a different spreadsheet.  I have got it working but only if I use the same format IDs that the original uses.  That is 1A1, 1A2, 2A1, 2A2 etc.  I want to use 111, 112, 211, 212 etc.

If I rename selected IDs as the original alphanumeric IDs, then it returns the correct result.  If I use the numeric IDs I get the N/A error.  I suspcet that it is the numbers as text thing but cannot see what to do next.

The spreadsheet is similar to those milage look up charts that you get in the front of road atlases where you can look up the milage between two cities.  My 'cities' are named 111, 112 etc and I am looking up the distance between them.

=VLOOKUP(LEFT(I8,3),lengthtable,MATCH(LEFT(O8,3),lengthtablecol,0),0)

I8 and O8 reference to the 'city' IDs in another worksheet  in a table called lengthtable that is in the same book.  lengthtablecol is the list of 'cities'.

If anyone can help with this I would be gratefull.

Thanks

R

Pingu

  • Put away those fiery biscuits!
  • Mrs Pingu's domestique
    • the Igloo
Re: Excel vlookup help required
« Reply #1 on: 06 December, 2018, 09:31:30 am »
It looks like your formula is searching in I8 and O8 on the current worksheet. Try adding the name of the worksheet the cells are on:

Code: [Select]
'worksheet name'!I8

Re: Excel vlookup help required
« Reply #2 on: 06 December, 2018, 09:32:53 am »
The LEFT() functions look redundant - remove those and use the I8/O8 numbers directly.

Re: Excel vlookup help required
« Reply #3 on: 06 December, 2018, 09:52:17 am »
The LEFT() functions look redundant - remove those and use the I8/O8 numbers directly.

If I do that I get a too many arguments message.

=VLOOKUP(I5,3,lengthtable,MATCH(O5,3,lengthtablecol,0),0)

Re: Excel vlookup help required
« Reply #4 on: 06 December, 2018, 09:52:57 am »
It looks like your formula is searching in I8 and O8 on the current worksheet. Try adding the name of the worksheet the cells are on:

Code: [Select]
'worksheet name'!I8

I'll try it but as I said it works with a different format ID.

Re: Excel vlookup help required
« Reply #5 on: 06 December, 2018, 09:59:06 am »
The LEFT() functions look redundant - remove those and use the I8/O8 numbers directly.

If I do that I get a too many arguments message.

=VLOOKUP(I5,3,lengthtable,MATCH(O5,3,lengthtablecol,0),0)

You need to remove the “,3” part too!

Re: Excel vlookup help required
« Reply #6 on: 06 December, 2018, 10:13:57 am »
That seems to work!  I'll give it a good road test shortly.

YCAF strikes again!

Thanks