Main Area and Open Discussion > General Software Discussion

autofill in EXCEL

<< < (4/4)

AndyM:
You need to have a table someplace in a worksheet that has all the info you will later want to reference.

You then reference that table in your lookup formula.

If you want "Jack", "42", and "981571" to appear when you type "Smith", then "Jack", "42", and "981571" all has to be in a table someplace to refer to.

If the info in Excel's help files isn't clear, a google search will list a multitude of primers, examples, and explanations on vlookup, hlookup, index, match, etc. (all methods of locating data in a table).

tsaint:
Kalos,
 your error (I guess) is because when you copied the example formulae, you didn't set up "Datalist". In your screenshot, it looks like excel doesn't know what "Datalist" is.
You need to highlight the cells which make up your table of data (F2:I4 in the example) and then name that cell range with the name "Datalist".
Then the formulae in the example should work.
How probably depends on your version of excel. In mine, I highlight the range of cells, right click, then select "name a range"
tony

Target:
You need to highlight the cells which make up your table of data (F2:I4 in the example) and then name that cell range with the name "Datalist".
-tsaint (May 02, 2009, 11:09 PM)
--- End quote ---

not actually necessary, you can reference the range directly, eg in cell A2 th formula would be =vlookup(B2,$f$1:$I$4,2,false)

Note that the range reference needs to be absolute or else the range will move as you autofill (that's what the dollar signs are for)

kalos:
that's sweet, it worked

however it appears #N/A in the fields, is there any way to 'hide' it?

Target:
formula changes to  =if(iserror(vlookup(B2,$f$1:$I$4,2,false)),"" =vlookup(B2,$f$1:$I$4,2,false))

Navigation

[0] Message Index

[*] Previous page