VLOOKUP - Better alternative for my spreadsheet?

[JamieP89]

Hey Excel friends!

So I have a bit of a different one today that I'm not sure on.

In the below you can see I have written a VLOOKUP with IFS to meet 3 conditions, and it's doing what I want (superseeding each column based on order of vlookup and data that is input in the table)

My worry with this, and as I seem to be experiencing in another worksheet as it's getting bigger is that the VLOOKUP i'm using in a real world scenario is going to another workbook and seems to be gradually slowing my workbook down (freezes very often) and have to wait for it to unfreeze.

The thing with this is that each row I create needs to have the VLOOKUP in as I need the data to be refreshing on each project for a certain number like in my example.

The question is, is there a better way to write this or better formula that will achieve this?

Book1
	B	C	D	E	F	G	H	I	J	K
5
6							Project	Least Important	More Important	Most Important
7	Project	Importance					ARDENTREWARD	7	10
8	ARDENTREWARD	10					WANTEDLEVEL	5
9							HOTARCH	8	4	10
10							MANYBAIT	5	6	1
11							POSSIBLEVOICE	5
12							UNINTERESTEDYAK	6	3
13							FRANKSMASH	1	9
14							CARINGDOLL	3		5
15							SUPERBSHOES	7	10	2
16							MESSYROLL	1	4
17							DIZZYTHUNDER
18							SUBSEQUENTPLANTS	2	7	4
19							PRIZEDHOPE	8
20							OUTSTANDINGSMELL	7	7
21							UPTIGHTNECK	6		2
22							LAMEPARCEL	4	4	1
23							LAWFULBUBBLE	1	4	3
24							STEADYENGINE	2	2	1
Sheet1

Cell Formulas
Range	Formula
C8	C8	=IFS(VLOOKUP(B8,H7:K24,4,FALSE),VLOOKUP(B8,H7:K24,4,FALSE),VLOOKUP(B8,H7:K24,3,FALSE),VLOOKUP(B8,H7:K24,3,FALSE),VLOOKUP(B8,H7:K24,2,FALSE),VLOOKUP(B8,H7:K24,2,FALSE))

 

[Alex Blakenburg]

What is the LET doing?

Here are a couple of references:
https://www.myonlinetraininghub.com/excel-let-function
Excel LET function with formula examples

Some more options:

Book3
	A	B	C	D	E	F	G	H	I	J	K
5			Xlookup * 2	Xlookup * 2 with Let	Xlookup Index-Match	Xlookup Index-Match with Let
6								Project	Least Important	More Important	Most Important
7		Project	Importance					ARDENTREWARD	7	10
8		ARDENTREWARD	10	10	10	10		WANTEDLEVEL	5
9		POSSIBLEVOICE	5	5	5	5		HOTARCH	8	4	10
10		UNINTERESTEDYAK	3	3	3	3		MANYBAIT	5	6	1
11								POSSIBLEVOICE	5
12								UNINTERESTEDYAK	6	3
13								FRANKSMASH	1	9
14								CARINGDOLL	3		5
15								SUPERBSHOES	7	10	2
16								MESSYROLL	1	4
17								DIZZYTHUNDER
18								SUBSEQUENTPLANTS	2	7	4
19								PRIZEDHOPE	8
20								OUTSTANDINGSMELL	7	7
21								UPTIGHTNECK	6		2
22								LAMEPARCEL	4	4	1
23								LAWFULBUBBLE	1	4	3
24								STEADYENGINE	2	2	1
Sheet2

Cell Formulas
Range	Formula
C8:C10	C8	=XLOOKUP(TRUE,ISNUMBER(XLOOKUP($B8,$H$4:$H$22,$I$4:$K$22)),XLOOKUP($B8,$H$4:$H$22,$I$4:$K$22),"",0,-1)
D8:D10	D8	=LET(ProjRow,XLOOKUP($B8,$H$4:$H$22,$I$4:$K$22),XLOOKUP(TRUE,ISNUMBER(ProjRow),ProjRow,"",0,-1))
E8:E10	E8	=XLOOKUP(TRUE,ISNUMBER(INDEX($I$6:$K$24,MATCH($B8,$H$6:$H$24,0),0)),INDEX($I$6:$K$24,MATCH($B8,$H$6:$H$24,0),0),"",0,-1)
F8:F10	F8	=LET(ProjRow,INDEX($I$6:$K$24,MATCH($B8,$H$6:$H$24,0),0),XLOOKUP(TRUE,ISNUMBER(ProjRow),ProjRow,"",0,-1))

 

[JamieP89]

That just allows you to store calculations in a variable, so they are only calculated once.

Interesting, and how is the LOOKUP working with the 2,1/(a<>0 ?? I seem to recall someone once telling me the 2/1 does something to the number but can't remember how exactly

 

[JamieP89]

Here are a couple of references:
https://www.myonlinetraininghub.com/excel-let-function
Excel LET function with formula examples

Some more options:

Book3
	A	B	C	D	E	F	G	H	I	J	K
5			Xlookup * 2	Xlookup * 2 with Let	Xlookup Index-Match	Xlookup Index-Match with Let
6								Project	Least Important	More Important	Most Important
7		Project	Importance					ARDENTREWARD	7	10
8		ARDENTREWARD	10	10	10	10		WANTEDLEVEL	5
9		POSSIBLEVOICE	5	5	5	5		HOTARCH	8	4	10
10		UNINTERESTEDYAK	3	3	3	3		MANYBAIT	5	6	1
11								POSSIBLEVOICE	5
12								UNINTERESTEDYAK	6	3
13								FRANKSMASH	1	9
14								CARINGDOLL	3		5
15								SUPERBSHOES	7	10	2
16								MESSYROLL	1	4
17								DIZZYTHUNDER
18								SUBSEQUENTPLANTS	2	7	4
19								PRIZEDHOPE	8
20								OUTSTANDINGSMELL	7	7
21								UPTIGHTNECK	6		2
22								LAMEPARCEL	4	4	1
23								LAWFULBUBBLE	1	4	3
24								STEADYENGINE	2	2	1
Sheet2

Cell Formulas
Range	Formula
C8:C10	C8	=XLOOKUP(TRUE,ISNUMBER(XLOOKUP($B8,$H$4:$H$22,$I$4:$K$22)),XLOOKUP($B8,$H$4:$H$22,$I$4:$K$22),"",0,-1)
D8:D10	D8	=LET(ProjRow,XLOOKUP($B8,$H$4:$H$22,$I$4:$K$22),XLOOKUP(TRUE,ISNUMBER(ProjRow),ProjRow,"",0,-1))
E8:E10	E8	=XLOOKUP(TRUE,ISNUMBER(INDEX($I$6:$K$24,MATCH($B8,$H$6:$H$24,0),0)),INDEX($I$6:$K$24,MATCH($B8,$H$6:$H$24,0),0),"",0,-1)
F8:F10	F8	=LET(ProjRow,INDEX($I$6:$K$24,MATCH($B8,$H$6:$H$24,0),0),XLOOKUP(TRUE,ISNUMBER(ProjRow),ProjRow,"",0,-1))

Oh cool! thanks for the other suggestions, I will have a look at these also

 

[Fluff]

This part 1/(a<>0)returns an array 1s or Div0 errors & the lookup then returns the last value that is closes to 2

 

[JamieP89]

This part 1/(a<>0)returns an array 1s or Div0 errors & the lookup then returns the last value that is closes to 2

Thank you! No sure I still fully understand what you mean, that's on me though...

Any resources on this and maybe I can break it down further in my own simple English (Sorry this is a new territory for me)

 

[Fluff]

Have a look here Excel formula: Get value of last non-empty cell | Exceljet ExcelJet has a lot of information on all the functions.

 

[Alex Blakenburg]

how is the LOOKUP working with the 2,1/(a<>0 ?

I had a quick look at the various sites for the Lookup including the ExcelJet one. If that one doesn't speak to you try this one:
Excel LOOKUP Function Examples with Video and Workbook

For the rest of what you are asking just keep in mind that in Fluff's formula,
FILTER(I7:K24,H7:H24=B8) has been assigned to the letter "a" so wherever you see "a" after that it is using the result of that Filter function.

 

[JamieP89]

I had a quick look at the various sites for the Lookup including the ExcelJet one. If that one doesn't speak to you try this one:
Excel LOOKUP Function Examples with Video and Workbook

For the rest of what you are asking just keep in mind that in Fluff's formula,
FILTER(I7:K24,H7:H24=B8) has been assigned to the letter "a" so wherever you see "a" after that it is using the result of that Filter function.

So essentially I could create the same formula assigned to "b" but have it only on certain cell? meaning I am giving the formula a variable (name)?

 

[Alex Blakenburg]

Correct. The let components are all pairs except the last one which is the final calculation that gives the result.
All the pairs are:
VariableName, Expression or Range
VariableName, Expression or Range
repeat until the very last one which is just
Final Calculation
Each Variable can use any of the Variables defined to the left of it ie they are evaluated left to right.

Particularly handy when as in the formulas we have used you need to use the same Expression twice or more in a formula.
eg if the lookup produces a certain result, then do something, else return the loopup value (Lookup is being performed once in the test part of the If and then again to actually return the value)

 

[JamieP89]

Have a look here Excel formula: Get value of last non-empty cell | Exceljet ExcelJet has a lot of information on all the functions.

Thanks for this had some time today to look at the content. I think I get it - I'll try an explain and see if I am right with how I understand it.

=LET(a,FILTER(I7:K24,H7:H24=B8),LOOKUP(2,1/(a<>0),a))

=LET (This is assigning my formula a variable for quick calling), "a" (This is giving it a name), "FILTER" (This is calling the FILTER formula to be used as our assigned variable) "Calculation" (Stating the logic), "LOOKUP" (allows us to look up out vector and value), "2" Is our BIG number we are looking for and will never find, "1/(a<>0)" (is dividing 1 by our calculation as either a 1 or DIV/0 - and in our case all 1's until it finds the last instance of this), "a" is the range were are calling from the LET function))

I've tried it several ways in practice with random columns and rows and it works how I would expect, so assuming this is fairly right.

Is this fundamentally close?