Excel Vlookup Function

Question

I have a spreadsheet that contains two worksheets. Each Worksheet contains the C column which I need to only extract the last numbers after the dash. So in Cell D it does this by using the formula:

=TRIM(RIGHT(SUBSTITUTE(C2,"-",REPT(" ",100)),100))

and the correct number is shown in Cell D as you can see.

A   B   C                        D  E
250 stevem  GP CONSTRUCCION-9-50399 50399   4/12/2013 0:00
223 stevem  ANIMATIC MEDIA-9-50400  50400   4/12/2013 0:00
224 stevem  DIGITAL ENGINEE-9-50401 50401   4/12/2013 0:00

I then need to do a vlookup on cell D in sheet 1 and match it with Cell D in sheet two which contains the same formula for extracting only the last digits after the -

My Vlookup is:

=VLOOKUP(D:D,Sheet2!A:F,2)

but I think because the lookup cell does not contain Just values, but rather a formula that returns a value, I get an error #N/A

Will Vlookup work for what I am trying to do?

AxGryndr AxGryndr · Accepted Answer · 2013-04-15T19:46:24

(#N/A) returned by a VLookup means that the value was not found. My guess is that the issue is you are looking for values of D from Sheet 1 in column A of Sheet 2.

The VLookup function has parameters of [Lookup Value],[Table Array],[Column Index Num],[Range Lookup]

Lookup Value is the value you are searching with. Table Array is the range of values to search in. The first column is what is searched. Column Index Num is the column number of the table array whose value should be returned if the Lookup value is found. If there are multiple instances of the Lookup value in the table array then the function returns the first instance. Range Lookup is true or false and determines if you only use and exact match -False, or a near match -True.

The table array you select is Sheet2!A:D and the column index number you have is 2. This means the vlookup is looking for the value in column A and if it is found will return the value in column B. If the value is not found then the vlookup returns a general error (#N/A). If needed you could capture this with the IFERROR() function wrapped around your VLOOKUP() function.

You describe you want to search for the value in column D of sheet 2 but you don't express what value you want returned. If you want just the number returned then setting the range to only one column will suffice.

Example: =vlookup(D1,Sheets2!D:D,1,False)

This VLOOKUP() function is looking for the value D1 in the column D on Sheet 2 and if it is found it returns the number otherwise it returns (#N/A) and does an exact match search.

Excel Vlookup Function

1 Answers