My longest ever Excel formula

Separate forum to permit easy exclusion when searching for serious information !
sjs
Posts: 1306
Joined: 24 Jan 2010, 10:08pm
Location: Hitchin

Re: My longest ever Excel formula

Post by sjs »

Manc33 wrote: 6 Dec 2021, 8:57am
axel_knutt wrote: 4 Dec 2021, 11:21amWhat's your biggest spreadsheet...
Not as big as yours :lol: My biggest was probably the ones I was trying to do for cryptocurrencies but I can't even remember what I was trying to work out and I do remember it not making one iota of difference (past results had no bearing or "edge" on future outcomes).

My long one got fixed by the way :P

This does the exact same thing as the thing in the first post:

Code: Select all

=VLOOKUP($X$3,$B$2:$K$21,10,FALSE)
It wasn't me that fixed it and I'm still not sure what that "10" is for, probably the amount of columns. Who knows what happens if a vaule in a random cell in that range like F12 is equal to something in the K column... luckily the way I have it all setup, the K column figures are in the hundreds or thousands whereas the main body of the table (C to J) is single and double digits.

Now I'm trying to do "if this is greater than that and the other is greater than another, display this text" but that's just a case of nesting about 4 ifs, not 20. :)
The 10 refers to the fact that column K is column 10 if you start with column B. Columns in between are ignored, if I understand the help facility correctly.
User avatar
honesty
Posts: 2658
Joined: 16 Mar 2012, 3:33pm
Location: Somerset
Contact:

Re: My longest ever Excel formula

Post by honesty »

Stop using vlookup and start using index and match. The cost of running it is far lower so speeds up your spreadsheet.
Manc33
Posts: 2218
Joined: 25 Apr 2015, 9:37pm

Re: My longest ever Excel formula

Post by Manc33 »

honesty wrote: 6 Dec 2021, 9:32am Stop using vlookup and start using index and match. The cost of running it is far lower so speeds up your spreadsheet.
I have no idea how to.
We'll always be together, together on electric bikes.
User avatar
[XAP]Bob
Posts: 19793
Joined: 26 Sep 2008, 4:12pm

Re: My longest ever Excel formula

Post by [XAP]Bob »

VLookup - that 10 is the column index of the column you want to return within the range in the first parameter.
Only the first column in the range is searched, only the numbered column is returned.
A shortcut has to be a challenge, otherwise it would just be the way. No situation is so dire that panic cannot make it worse.
There are two kinds of people in this world: those can extrapolate from incomplete data.
User avatar
simonineaston
Posts: 8003
Joined: 9 May 2007, 1:06pm
Location: ...at a cricket ground

Re: My longest ever Excel formula

Post by simonineaston »

I used to use Excel at work to solve simple problems and enjoyed finding out its not inconsiderable features - I found this YouTuber helpful. Her explanation of Index & Match is brief and easy to understand.
S
(on the look out for Armageddon, on board a Brompton nano & ever-changing Moultons)
User avatar
Cowsham
Posts: 4963
Joined: 4 Nov 2019, 1:33pm

Re: My longest ever Excel formula

Post by Cowsham »

simonineaston wrote: 23 Dec 2021, 11:49am I used to use Excel at work to solve simple problems and enjoyed finding out its not inconsiderable features - I found this YouTuber helpful. Her explanation of Index & Match is brief and easy to understand.
and this girl
I am here. Where are you?
Post Reply