VLookup

[anewday777]

Im inputting this formula but it is not pulling the result

=VLOOKUP(B2,Sheet1!A120:D136,4,FALSE)

It gives a result of N/A

 

[LouisT]

can you give us a sample of what is in B2 as well as the Range?

 

[anewday777]

b2 has terminal id [TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl66, width: 136"]P361985[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1917"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 136"]
<colgroup><col></colgroup><tbody>[TR]
[TD]P361985[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[anewday777]

[TABLE="width: 401"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Terminal[/TD]
[TD]Name[/TD]
[TD]Machine Type[/TD]
[TD]Install Date[/TD]
[/TR]
[TR]
[TD]P392607 [/TD]
[TD]TRISTATE ATM HSPC 258[/TD]
[TD]3000 (Standard 3 DCC) GenMega[/TD]
[TD="align: right"]7/24/18[/TD]
[/TR]
[TR]
[TD]P392608 [/TD]
[TD]TRISTATE ATM TBF 259[/TD]
[TD]2500 (Standard 3 DCC) GenMega[/TD]
[TD="align: right"]7/24/18[/TD]
[/TR]
[TR]
[TD]P394659 [/TD]
[TD]TOWNPLACE SUITES BY MARRIOTT 038[/TD]
[TD]2500 (Standard 3 DCC) GenMega[/TD]
[TD="align: right"]8/10/18[/TD]
[/TR]
[TR]
[TD]P395722 [/TD]
[TD]PLANO MUNICIPAL COURT 284[/TD]
[TD]2700 (Std 3 on P5)Hyosung[/TD]
[TD="align: right"]8/31/18[/TD]
[/TR]
[TR]
[TD]P395723 [/TD]
[TD]PSA MURPHY 057[/TD]
[TD]Halo (Std 3 on P5)Hyosung[/TD]
[TD="align: right"]8/25/18[/TD]
[/TR]
[TR]
[TD]P395724 [/TD]
[TD]BELLEZZA WINDHAVEN 062[/TD]
[TD]5000 (Std 3 on P5)Hyosung[/TD]
[TD="align: right"]8/25/18[/TD]
[/TR]
[TR]
[TD]P395725 [/TD]
[TD]JESUIT 063[/TD]
[TD]2700 (Std 3 on P5)Hyosung[/TD]
[TD="align: right"]8/30/18[/TD]
[/TR]
[TR]
[TD]P395726 [/TD]
[TD]TRANS AMERICA 067[/TD]
[TD]2700 (Std 3 on P5)Hyosung[/TD]
[TD="align: right"]8/31/18[/TD]
[/TR]
</tbody>[/TABLE]

 

[anewday777]

both sheets show tid, i was using tid and looking at other sheet to find the tid and return the install date

 

[anewday777]

the error under the yellow exclamation is "value not available?

 

[LouisT]

Based on this information and what you're trying to return, there's only a couple reasons for the N/A

1 - The range and or reference is wrong(unlikely i assume)
2 - The TID is actually not in the list
3 - The TID reference and the TID in the range are perhaps not formatted the same and/or one has perhaps a Space in it. example "P361985" in one cell but "P361985 " in another.

Make sense?
Louis T

 

[sturnusek]

If you are using Power Query, make sure you identified the column as a text/number. If you are using this formula in more than one place, make sure you lock the range using the $ signs, as if you drag it down it will change.

 

[anewday777]

I have another spreadsheet it works fine on the formulas different data but still using tid.....for some reason.....i still cannot get it to work

 

[LouisT]

I'd check specifically that data. the only thing that makes sense is that there's an extra/different character in the string.