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))

 

[Fluff]

Firstly I would recommend against using IFS, you should use nested IF functions instead as they are far more efficient.
Are you trying to find the last value in the row?

 

[JamieP89]

Firstly I would recommend against using IFS, you should use nested IF functions instead as they are far more efficient.
Are you trying to find the last value in the row?

Ah okay - I'll need to look at how I would do that.

Not so much, What the formula is doing for me is looking at the last column, is there a number, if not move to next column, if no number move to first column.

 

[Fluff]

Ok, how about

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

 

[MaxD]

Edit: After your second post, I realise this formula doesn't give you what you're after. @Fluff formula works though.

This code gives you the maximum value across the 3 columns regardless of which column it is in.

=MAX(IF(H7:H24=B8,I7:K24))

 

[Fluff]

That will return the max value, which is not always the last value on the row.

 

[JamieP89]

Ok, how about

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

Would this be less intensive that my original formula?

Also are you able to explain what this formula is doing?

 

[Fluff]

Would this be less intensive that my original formula?

Yes, your formula is running 6 vlookups whenever the data or B8 changes, whereas my formula is running a single filter & lookup.

The filter will return the row that matches B8 & the lookup will find the last non 0 value.

 

[JamieP89]

Yes, your formula is running 6 vlookups whenever the data or B8 changes, whereas my formula is running a single filter & lookup.

The filter will return the row that matches B8 & the lookup will find the last non 0 value.

Thanks for clarifying

What is the LET doing?

 

[Fluff]

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