VLOOKUP returning #N/A, can't figure out why...?

[doctorevii2001]

I recently created my first spreadsheet using the VLOOKUP and it worked great! I thought it would be convenient to add the function to an existing spreadsheet, however something is not working right as it's returning me the dreaded #N/A for the results. Here's the formula I'm trying:

=(IF($P$1="","",(VLOOKUP($P$1,Table1,1,FALSE))))

the cell P1 is where I type in my search term (in this case a confirmation number from a supplier). Then in all the other columns on row 1 I want it to return the results that are associated with the confirmation number I type in P1.

Here's a screenshot showing the top row and some examples of the table below. Yes, the table is correctly specified in the formula as "Table1".

I appreciate any help!

http://1drv.ms/1LoIcT8

 

[etaf]

return the results that are associated with the confirmation number I type in P1.

what does that mean in excel terms
just use
VLOOKUP($P$1,Table1,1,FALSE)
to test

check the value exists and that there is not a space before or after

do a =P1 = cell with the value you think is the same as P1


The 1 in the vlookup means it will return the 1st column in the table1
and also P1 will be looking in the 1st column

 

[doctorevii2001]

what does that mean in excel terms

Thanks for your quick reply... no go on the solution, but here's more info:

So cell P1 is where I want to be able to type the information that I want searched (which is an acknowledgement number). My goal is to type the number in there and then in the cells to the left and right of P1 (so cells A1:AB1 with the exception of P1, which I have manually filled in) to auto-populate with the information associated with the acknowledgement number I typed in. Basically it's to save me from having to sort or page through all of the info on the page and pull up just the item I need to check on). I did a similar thing on another spread sheet and it worked perfectly, so I'm confused what is going wrong with this one.

I tried the formula without the "if" argument and got the same #N/A results. Is it possible to upload the spreadsheet for someone to view? I couldn't figure it out on this website.

Thanks for your time and help!

 

[etaf]

on a share like onedrive or dropbox

vlookup does not look left , only right

 

[Aladin Akyurek]

on a share like onedrive or dropbox

vlookup does not look left , only right

A link to an outside file is not desirable because they will not be accessible at some point when the reverse is true for this board... See also: http://www.mrexcel.com/forum/about-board/880179-posting-attachments.html#post4264741

 

[doctorevii2001]

on a share like onedrive or dropbox

vlookup does not look left , only right

Ahh... So maybe that's my problem? Is there a different formula to accomplish what I'm going for?

Here are links for both the sheet in question as well as my other one (my first foray into VLOOKUP) that works as advertised.

Thanks again!

Need help with this one: http://1drv.ms/1l8gFA3
This one works: http://1drv.ms/1l8gK6G

 

[sixfyl]

I didn't download the worksheet because I don't want to download a macro enabled book but I think you need INDEX/MATCH. Try =INDEX(Table1,MATCH(the exact range that column p goes to in Table1,0),(2, 3, etc. depending on the column

The way this works is the Index function returns a value at a particular row and column. The match function tells the index function which row to get and the 1, 2, 3, etc specifies the column.

Edit: Also, judging by your screen shot it might be possible some of your values in column P are numbers stored as text since a lot of the data is alpha numeric. If this is the case and you just type 1186558 into cell P1 it will be a type mismatch and won't work.

 

[doctorevii2001]

I didn't download the worksheet because I don't want to download a macro enabled book but I think you need INDEX/MATCH.

Whoops, can't blame you there. Here's a link to the same spreadsheet sans-macros (just copied and pasted the page in question to a new document):

http://1drv.ms/1Oi30QQ

I tried the other index formula, but I'm not sure I quite grasp how to use it... Didn't google yet, though, been tied up on another job.

Thanks again for your time and input!

 

[sixfyl]

It's giving me an internal server error for that now so I still can't see it. Anyway, the INDEX function has three arguments: array, row_num, col_num. Array is the data you want to search, in your case table1. The row_num is just that, the row in the array you want to find. The column number is the column you want to find. So if I have =INDEX(A1:C3,2,3) it will return the value in cell C2 (the second row and third column of the array.

The INDEX function alone isn't that useful. It works best when it's combined with the MATCH function. The MATCH function returns the position of a value within a range. You use it with the INDEX function because the output of MATCH gives you your row_num and/or col_num. It has three arguments, lookup_value, lookup_array, and match_type. In your case, the lookup_value would be cell P1 and you want a match type 0 or exact match. The lookup_array would just be column P of however long table1 is. So if table1 is 100 rows, you need your lookup_array to go exactly 100 rows as well.
Say the number in P1 is found in row 60. If you type in cell B1 =INDEX(table1,MATCH($P$1,$P$3:$P:$103,0),2) the function would find the match in the 60th row and simplify to =INDEX(table1,60,2). This tells it to pull the value in the 60th row and second colymn of table 1.