0

I have a formula that works, and I am dragging it across a field of things.

=VLOOKUP($AP$6,DaysEditor!$A:$ND,COLUMN(AH:AH), FALSE)

So i am using the Value in AP6 (Employee name) to search in column A (Employee names) on DaysEditor sheet, and then return the value in column AH. This works perfectly. But we are changing it to lookup via our employee code, which is now located in column NL. I have a bunch of macros written referencing cell columns, so i would VERY MUCH prefer not to have to move column NL to before column A. So i found INDEX(MATCH()). I thought this would solve my issue, but I cannot seem to get it to work. It keeps returning #N/A.

=INDEX(DaysEditor!AH:AH,MATCH($AP$6,DaysEditor!$NL:$NL,0))

From my understanding of this, I am returning the value in column AH for the match of A6 in column NL. But it doesnt seem to work. Any help is appreciated!

edit: The new value that will be in AP6 that we will look for in NL will be a either 8 letters or 7 letters and 2 numbers at the end. For instance dogscatt or catsdog01. The value in the column we are returning (AH in this instance) will be 1 letter only. No numbers.

Edit2: I was able to figure it out by changing the ranges to actual ranges and not columns worked. 

=INDEX(DaysEditor!AH$6:AH$200,MATCH($AP$8,DaysEditor!$NL$6:$NL$200,0))
Lzypenguin
  • 945
  • 1
  • 7
  • 18
  • What kind of values are we talking about? – JvdV Dec 15 '19 at 11:07
  • @JvdV The new value that will be in AP6 that we will look for in NL will be a either 8 letters or 7 letters and 2 numbers at the end. For instance dogscatt or catsdog01. The value in the column we are returning (AH in this instance) will be 1 letter only. No numbers. – Lzypenguin Dec 15 '19 at 11:13
  • So the formula looks alright, maybe there are trailing spaces in either your lookup value or lookup range. A more general note, get in the habit of always choosing index and match over vlookup. [Here](https://stackoverflow.com/a/58436658/9758194) is why – JvdV Dec 15 '19 at 11:30
  • @JvdV I have checked all the spacing and everything looks correct. I have no clue why its not working. I would love to start using INDEX MATCH if i could actually get one of them to work..... IDK if this matters, but some of the cells in the AH column (The column to get values from) will be empty. But they still work with VLOOKUP and even on cells that arent empty i cant get the INDEX MATCH to work. – Lzypenguin Dec 15 '19 at 11:39
  • What value is it returning now? A NA error? – JvdV Dec 15 '19 at 11:59
  • In the vlookup you used `DaysEditor!$A:$ND` as range, but in indexMatch you use `DaysEditor!$NL:$NL` as range .. why the difference? – p._phidot_ Dec 15 '19 at 12:09
  • @p._phidot_ My table consists of values from colum A to column ND. I added this employee code column in column NL. What i understood from VLOOKUP was they it was looking for AP6 in the range A:ND (which was located in column A) but in Index match I was specifically telling it which range to look in for this value, which is NL. – Lzypenguin Dec 16 '19 at 00:04
  • "It keeps returning #N/A" > an indication that indexMatch cannot find a match in the range specified. | I'm not an expert in using vlookup, neither good at using CSE (ctrl-Shft-Enter) formula.. but afaik for indexMatch, the logic is same : find the 1st match in 'a' in a list say : {"j","e","t","y","a","f"}, and return 7 from {2,3,4,8,7,9} will look like `=index( {2,3,4,8,7,9} ,match("a",{"j","e","t","y","a","f"},0))`. making sure that the input/output list dimension having the same size helps to reduce the headache. – p._phidot_ Dec 16 '19 at 06:25
  • Since we don't have a sample of your data structure, it is hard for us to say whether your range is defined correctly.. | so the best we can do here (without the sample data(with exactly the same cells location) , is to ask troubleshooting questions.. :p | (and my next (reason tracking /troubleshooting) question is.. with a sample input .. (say catsdog01), where is its exact in the `DaysEditor!$A:$ND` range ? – p._phidot_ Dec 16 '19 at 06:32

0 Answers0