Do 'nonsequential' PRNGs exist? It is my understanding that PRNGs typically use a seed and can then generate random values one after the other. This is what I mean by being 'sequential'.

How about a PRNG which allows me to retrieve e.g. the third value, without computing the first and second? Basically, I'm looking for a function $g: \mathbb{N} \rightarrow (\mathbb{N} \rightarrow \mathbb{R})$ such that for all seeds $n \in \mathbb{N}$, $g(n)$ is a function who's values follow some specified distribution while computing $g(n)(m)$ takes constant time.
How about a PRNG which allows me to retrieve e.g. the third value, without computing the first and second?


That is a popular informal description of what a pseudo-random function is—a function constructed out of a random seed that, for any input, produces a pseudorandom output in a repeatable fashion (i.e., same input always gives you the same pseudorandom output).

The suggestion in the comments to use CTR mode is along these lines—CTR mode is the application of a PRF to a sequence of counter values.  I.e, if $f : K \to I \to O$ is a pseudorandom function family, $k \in K$ is a randomly selected secret key, and $i_0, \dots, i_n \in I^*$ is a sequence of distinct values in the domain of $f_k$, then the sequence $f_k(i_0), \dots, f_k(i_n)$ cannot be efficiently distinguished from a sequence of values drawn at random from $O$.

Some practical choices of PRF would be:


HMAC
Any block cipher (e.g., AES). These are pseudorandom permutations—bijective PRFs—which means that they cannot be distinguished from a general PRF unless the number of queries exceeds the so-called "birthday bound" (search for "PRF-PRP switching lemma").