Excel Help
-
Need some help. Got a simple sheet contaning machine names and build numbers
So
A | BDesktop1 Microsoft Windows 10 Pro 10.0.19041
Desktop2 Microsoft Windows 10 Pro 10.0.19042
Desktop3 Microsoft Windows 10 Pro 10.0.18363
Desktop4 Microsoft Windows 10 Pro 10.0.19041
Desktop5 Microsoft Windows 10 Pro 10.0.19041
Desktop6 Microsoft Windows 10 Pro 10.0.19041
Desktop7 Microsoft Windows 10 Pro 10.0.19042What i need to do is, get column C to output a value based on what B contains. e.g.
IF B contains 18363 output 1909
IF B contains 19043 ouptut 21H1I've been trying all sorts of IF OR SUM SEARCH COUNT etc, just can't get it working
-
@hobbit666 said in Excel Help:
Desktop1 Microsoft Windows 10 Pro 10.0.19041
Desktop2 Microsoft Windows 10 Pro 10.0.19042
Desktop3 Microsoft Windows 10 Pro 10.0.18363
Desktop4 Microsoft Windows 10 Pro 10.0.19041
Desktop5 Microsoft Windows 10 Pro 10.0.19041
Desktop6 Microsoft Windows 10 Pro 10.0.19041
Desktop7 Microsoft Windows 10 Pro 10.0.19042Here
I have an IFS Formulas as below:
=IFS(F2=$B$12,$C$12,F2=$B$13,$C$13,F2=$B$14,$C$14,F2=$B$15,$C$15,F2=$B$16,$C$16,F2=$B$17,$C$17,F2=$B$18,$C$18)
With a list of the Windows 10 Builds that can be on another sheet or at the bottom.
-
@dbeato Thanks for that,
No idea how it works but it does
-
@hobbit666 said in Excel Help:
@dbeato Thanks for that,
No idea how it works but it doesThe IFS formula is just a bunch of "if" stacked together
=IFS(F2=$B$12,$C$12,F2=$B$13,$C$13,F2=$B$14,$C$14,F2=$B$15,$C$15,F2=$B$16,$C$16,F2=$B$17,$C$17,F2=$B$18,$C$18)
Is the same as:
if F2=B12 then result=C12
if F2=B13 then result=C13
if F2=B14 then result=C14
if F2=B15 then result=C15
etcSo basically just looking at the table with the windows build numbers and versions.
The
$inside the cell names is just to tell Excel what to do with it when you copy the formula to another cell.
$B$12 just means B12 will always be the absolute cell B12 regardless of where you copy the formula. -
Interesting.
The IFS statement breaks down like this.If $B$12 (which is column B, row 12), then enter value from $C$12 (i.e. the data from column C, row 12),
It keeps checking like this until there is a match, or the end is reached. -
@dashrender said in Excel Help:
Interesting.
The IFS statement breaks down like this.If $B$12 (which is column B, row 12), then enter value from $C$12 (i.e. the data from column C, row 12),
It keeps checking like this until there is a match, or the end is reached.Excel actually has a lookup function that works exactly like this. It would probably be better in this case. Less to write and easier to add more values.
=LOOKUP(F12, $B$12:$B$25, $C$12:$C$25)
Then you can add some more rows with version numbers if needed. Without changing any formulas.
-
@pete-s said in Excel Help:
The IFS formula is just a bunch of "if" stacked together
Is the same as:
if F2=B12 then result=C12
if F2=B13 then result=C13
if F2=B14 then result=C14
if F2=B15 then result=C15
etcYeah when i look at the whole thing like that makes sense
If $B$12 (which is column B, row 12), then enter value from $C$12 (i.e. the data from column C, row 12),
It keeps checking like this until there is a match, or the end is reached.
Was overwhelmed by all the $ in there
-
@hobbit666 said in Excel Help:
Desktop1 Microsoft Windows 10 Pro 10.0.19041
Desktop2 Microsoft Windows 10 Pro 10.0.19042
Desktop3 Microsoft Windows 10 Pro 10.0.18363
Desktop4 Microsoft Windows 10 Pro 10.0.19041
Desktop5 Microsoft Windows 10 Pro 10.0.19041
Desktop6 Microsoft Windows 10 Pro 10.0.19041
Desktop7 Microsoft Windows 10 Pro 10.0.19042No Need for a bunch of Nested IF= statements.
VLOOKUP is made for this. Although XLOOKUP is the new VLookup.
Here's the formula in Column I of my screen shot. Don't worry about the $, they just mean the formula should not auto advance when you copy and paste to other cells.
=VLOOKUP(H4,$C$21:$D$25,2,FALSE)
The "False" means EXACT Match, please leave it as False.
-
@jasgot said in Excel Help:
@hobbit666 said in Excel Help:
Desktop1 Microsoft Windows 10 Pro 10.0.19041
Desktop2 Microsoft Windows 10 Pro 10.0.19042
Desktop3 Microsoft Windows 10 Pro 10.0.18363
Desktop4 Microsoft Windows 10 Pro 10.0.19041
Desktop5 Microsoft Windows 10 Pro 10.0.19041
Desktop6 Microsoft Windows 10 Pro 10.0.19041
Desktop7 Microsoft Windows 10 Pro 10.0.19042No Need for a bunch of Nested IF= statements.
VLOOKUP is made for this. Although XLOOKUP is the new VLookup.
Here's the formula in Column I of my screen shot. Don't worry about the $, they just mean the formula should not auto advance when you copy and paste to other cells.
=VLOOKUP(H4,$C$21:$D$25,2,FALSE)
The "False" means EXACT Match, please leave it as False.Mostly correct, except he doe snot have the version split into it's own column.
He only has A and B as noted.
@hobbit666 said in Excel Help:
A | B
Desktop1 Microsoft Windows 10 Pro 10.0.19041So it would be more like this, assuming that the data starts in row 2 and the lookup table starts on Row 21
=VLOOKUP(RIGHT(B2,10),$A$21:$B$25,2,FALSE) -
@jaredbusch said in Excel Help:
Mostly correct, except he doe snot have the version split into it's own column.
He only has A and B as noted.
@hobbit666 said in Excel Help:
A | B
Desktop1 Microsoft Windows 10 Pro 10.0.19041So it would be more like this, assuming that the data starts in row 2 and the lookup table starts on Row 21
=VLOOKUP(RIGHT(B2,10),$A$21:$B$25,2,FALSE)True,
I did separate it to get the other ways working
But if i needed to redo it on the untouched data, this would work toThanks All
-
@jasgot said in Excel Help:
No Need for a bunch of Nested IF= statements.
VLOOKUP is made for this. Although XLOOKUP is the new VLookup.I did already mention LOOKUP in a couple of posts above.
But the power of the IFS is really when you just have a few different options and put them directly in the formula. Then you don't need a lookup table at all.
The SWITCH makes it even better as that is the equivalent to a case statement.
=SWITCH(F2, "10.0.19043", "21H1", "10.0.19042", "20H2", "10.0.19041", "2004", "10.0.18363", "1909", "10.0.17763", "1809", "OLD!")
