Vlookup Help

[JLouis]

I am trying to lookup a value in a separate workbook whose value contains a forward slash. The lookup value is "Total Disc / Coup" and the lookup doesn't work with the forward slash in it.

Here's formula:
=VLOOKUP("Total Disc / Coup",'C:\FH276 QB WEEKLY 2017\Royalty\[report.xls]sheet1'!$A$1:$N$50,14,FALSE)

Thanks for any assistance.

 

[Aladin Akyurek]

Are you getting #N/A? If so, that means Total Disc / Coup is not available in sheet1'!$A$1:$A$50...

 

[JLouis]

I have the range correct, Excel doesn't allow a lookup with values containing a forward slash.

 

[Aladin Akyurek]

I have the range correct, Excel doesn't allow a lookup with values containing a forward slash.

I wasn't talking about the correctness of "the range". If you get #N/A, that's because the match-range. i.e. the first column of your table range, does not house the string Total Disc / Coup, not because Excel does not allow for this string a forward slash...

Example:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]x[/td][td]

100​

[/td][td][/td][td][/td][td]

300​

[/td][/tr]
[tr][td]

3​

[/td][td]jad[/td][td]

200​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Total Disc / Coup[/td][td]

300​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]vad[/td][td]

400​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

E2 houses a formula similar to yours:

=VLOOKUP("Total Disc / Coup",Sheet1!$A$1:$B$20,2,0)

 

[JLouis]

It's returning a "0". I have the other sheet open. I tried the formula on the same sheet and got a zero. When I take out the forward slash it works. A Space problem perhaps? I actually copied and pasted the value into the formula so the text would be exact.

 

[Aladin Akyurek]

It's returning a "0". I have the other sheet open. I tried the formula on the same sheet and got a zero. When I take out the forward slash it works. A Space problem perhaps?

What should be the result?

 

[JLouis]

$52.81

Total Discounts $15.68
Total Coupon $37.13
Total Disc / Coup $52.81
Charge Deposits
Visa $1,006.56
Diners / Misc

 

[Aladin Akyurek]

In which column are those numbers?

 

[JLouis]

"A20" is the text and "N20" is the value

 

[JLouis]

BTW, I went to my laptop and was able to do as you say across two workbooks without problem. I am exporting the sheet off the internet in xls format. Would that affect the behavior of the worksheet?