VLOOKUP coming back as #N/A (some of the time)

Hi,

Here is the vlookup:

=VLOOKUP(RANDBETWEEN(1,10),'Formula Data'!$AD$5:$AE$14,2,FALSE)&" with "&VLOOKUP(RANDBETWEEN(1,47),'Formula Data'!$AB$5:$AC$51,2,FALSE)&" facing a "&VLOOKUP(RANDBETWEEN(1,10),'Formula Data'!$AF$5:$AG$14,2,FALSE)&" open from a "&VLOOKUP(RANDBETWEEN(1,6),'Formula Data'!AH5:AI10,2,FALSE)

It says the bolded part is volatile, but I cannot find why that is?

Any help is appreciated!!

Originally Posted by oeldere

this means the value you want to find is Not Available (N/A) in the VLookup table.

Hi,

I have triple checked this and I am 100% sure it is, please see the below picture (sorry about the quality/format):

https://gyazo.com/fc7fb4569aff29e56bf53ba2ba4b1838

The picture displays the vlookup table

Pictures usually aren't very helpful because we can't copy/paste your data into our own workbooks for troubleshooting.

Can you attach an actual workbook to this thread?

How do I attach it?

I have clicked "go advance" and clicked on the paperclip image to try and attach it, but nothing happens.

After 'Go Advanced', Instead of the paperclip, look down below for 'Manage Attachments'

Scroll down to "Manage Attachments": "Paper Clip" does not work!

Hehe, sorry for my newbieness; I am new to this forum!

It should be done now!

I have never used the INDEX function, what does it do?

Also, the vlookup+randbetween combo work fine with the other entries earlier in the function, so why is the last one volatile, but the others OK?

Thanks

Hi,

Using the INDEX function still returns errors....

I would assume it has something to do with the Floating Point Precision, causing the result of randbetween to not be an EXACT Whole value matching with the numbers in the lookup tables.

Try adding ROUND to each of the randbetween functions
ROUND(RANDBETWEEN(1,10),0)

Originally Posted by Jonmo1

I would assume it has something to do with the Floating Point Precision, causing the result of randbetween to not be an EXACT Whole value matching with the numbers in the lookup tables.

Try adding ROUND to each of the randbetween functions
ROUND(RANDBETWEEN(1,10),0)

Hi,

This is not it, as 90% of the returns would result in errors, and I use this formula combo regularly without issue.

Originally Posted by Tony Valko

Here's how you can use INDEX in this application:

=INDEX('Formula Data'!$AE$5:$AE$14,RANDBETWEEN(1,10))&" with "&INDEX('Formula Data'!$AC$5:$AC$51,RANDBETWEEN(1,47))&" facing a "&INDEX('Formula Data'!$AG$5:$AG$14,RANDBETWEEN(1,10))&" open from a "&INDEX('Formula Data'!AI5:AI10,RANDBETWEEN(1,6))

YAHOOOO, thanks!!!

If you don't mind, can you explain why this works and the vlookup doesn't? Thanks again!

AF10 on the Formula Data sheet = 3, but it seems you want that to be a 6.