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.Manc33 wrote: ↑6 Dec 2021, 8:57amNot as big as yours 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
This does the exact same thing as the thing in the first post:
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.Code: Select all
=VLOOKUP($X$3,$B$2:$K$21,10,FALSE)
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.
My longest ever Excel formula
Re: My longest ever Excel formula
Re: My longest ever Excel formula
Stop using vlookup and start using index and match. The cost of running it is far lower so speeds up your spreadsheet.
Re: My longest ever Excel formula
I have no idea how to.
We'll always be together, together on electric bikes.
Re: My longest ever Excel formula
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.
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.
There are two kinds of people in this world: those can extrapolate from incomplete data.
- simonineaston
- Posts: 8063
- Joined: 9 May 2007, 1:06pm
- Location: ...at a cricket ground
Re: My longest ever Excel formula
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)
(on the look out for Armageddon, on board a Brompton nano & ever-changing Moultons)
Re: My longest ever Excel formula
and this girlsimonineaston 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.
I am here. Where are you?