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

P361985

<tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

P361985

<colgroup><col></colgroup><tbody>
</tbody>

</body>

 

[anewday777]

Terminal	Name	Machine Type	Install Date
P392607	TRISTATE ATM HSPC 258	3000 (Standard 3 DCC) GenMega	7/24/18
P392608	TRISTATE ATM TBF 259	2500 (Standard 3 DCC) GenMega	7/24/18
P394659	TOWNPLACE SUITES BY MARRIOTT 038	2500 (Standard 3 DCC) GenMega	8/10/18
P395722	PLANO MUNICIPAL COURT 284	2700 (Std 3 on P5)Hyosung	8/31/18
P395723	PSA MURPHY 057	Halo (Std 3 on P5)Hyosung	8/25/18
P395724	BELLEZZA WINDHAVEN 062	5000 (Std 3 on P5)Hyosung	8/25/18
P395725	JESUIT 063	2700 (Std 3 on P5)Hyosung	8/30/18
P395726	TRANS AMERICA 067	2700 (Std 3 on P5)Hyosung	8/31/18

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.