lookup_value in VLOOKUP formula

**99f150** · 03-16-2010, 09:33 AM

In Workbook 1 I'm using the formula;

=IF(ISNA(VLOOKUP(C2,INDIRECT("'C:\Documents and Settings\user\Desktop\["&A1&".xls]Sheet1'!$a1:b$300"),2,0)),"Not Applicable",(VLOOKUP(C2,INDIRECT("'C:\Documents and Settings\user\Desktop\["&A1&".xls]Sheet1'!$A1:B$300"),2,0)))

to grab data from Workbook 2.

My question is, rather than having to specify the lookup_value in cell C2, can I specify the value directly in the formula itself.

Example: if I want to return the value from Workbook 2 when "Example" is entered in cell C2. Instead of having to enter "Example" in C2 could the formula somehow have the word "Example" in it for the lookup_value.

Thanks for the help.

**NBVC** · 03-16-2010, 09:37 AM

Just replace C2 in the formula with the word Example, enclosed in double quotes....( "Example" )

**99f150** · 03-16-2010, 09:48 AM

That's what I thought I should do as well but when I tried it all I get is the "Not Applicable" even though a value does exist.

**NBVC** · 03-16-2010, 09:50 AM

There are 2 occurances of C2 in your formula: Should look like this:

=IF(ISNA(VLOOKUP("Example",INDIRECT("'C:\Documents and Settings\user\Desktop\["&A1&".xls]Sheet1'!$a1:b$300"),2,0)),"Not Applicable",(VLOOKUP("Example",INDIRECT("'C:\Documents and Settings\user\Desktop\["&A1&".xls]Sheet1'!$A1:B$300"),2,0)))

**99f150** · 03-16-2010, 09:55 AM

Sorry NBVC,

I should've mentioned I changed both values with no luck.

Thanks for the help!

**NBVC** · 03-16-2010, 10:10 AM

Well, if it worked with C2 having the exact word Example (without quotes), then it should, theoretically work replacing C2 with "Example" (with quotes).

I would need to see sample workbooks to see why it doesn't work then...

Are you positive that the sheet referenced in A1, and in column A1:B300, the word "Example" exists exactly as you typed it in the formula.. and without extra spaces, etc...

**NBVC** · 03-16-2010, 11:21 AM

As I mentioned above:

Originally Posted by NBVC

..
Are you positive that the sheet referenced in A1, and in column A1:B300, the word "Example" exists exactly as you typed it in the formula.. and without extra spaces, etc...

**99f150** · 03-16-2010, 11:03 AM

Figured it out.

The values that I am searching for are genreated by another program that creates a spreadsheet. The program was inserting a space after the value, which I didn't put in.

Thanks again NBVC