How to vlookup across multiple columns? If blank, then next column

[js6425]

What is the best way to create a vlookup to do the following?

Lookup Value in O123
Vlookup and pull the corresponding value in column 30
If row 30 is blank, then pull the value from column 29
If column 29 is blank, then pull the value from column 28

Does that make sense? Been trying to figure this out all afternoon and am stumped. Thanks!

 

[earthworm]

use iferror then on each error apply vlookup to select value from another example keep doing unless all columns are covered

 

[StephenCrump]

Welcome to the Forum!

Do you mean something like this?

	A	B	C	D	E	F	G	H	I	J
1
2	Lookup			LookupTable
3	B	19		A	2	3	5	7	11
4				B	13		17	19
5				C			23
6				D
7
Sheet1

Cell Formulas
Range	Formula
B3	B3	=LET(r,INDEX(D3:I6,MATCH(A3,D3:D6,),),x,FILTER(r,r<>""),IF(COLUMNS(x)=1,"No values",INDEX(x,COLUMNS(x))))

 

[Peter_SSs]

@StephenCrump
Hi Stephen
I think that you might need an extra check for "No values".
One for if there are no values in the matching row (you have this check), and ..
One for if the lookup value does not exist in column D

One adaptation might be:

=LET(n,"No values",rw,INDEX(FILTER(E3:I6,D3:D6=A3,n),1,0),c,FILTER(rw,rw<>"",n),INDEX(c,COUNTA(c)))

Another slightly different approach might be:

22 02 09.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Lookup			LookupTable
3	B	19		A	2	3	5	7	11
4				B	13		17	19
5				C			23
6				D
Lookup

Cell Formulas
Range	Formula
B3	B3	=LET(rw,INDEX(FILTER(E3:I6,D3:D6=A3,""),1,0),XLOOKUP(TRUE,rw<>"",rw,"No values",,-1))

 

[js6425]

Thanks! Is there any way to do this without the LET function?

 

[Peter_SSs]

Thanks! Is there any way to do this without the LET function?

Yes, this is only marginally longer & less efficient

22 02 09.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Lookup			LookupTable
3	B	19		A	2	3	5	7	11
4				B	13		17	19
5				C			23
6				D
Lookup (2)

Cell Formulas
Range	Formula
B3	B3	=XLOOKUP(TRUE,INDEX(FILTER(E3:I6,D3:D6=A3,""),1,0)<>"",INDEX(FILTER(E3:I6,D3:D6=A3,""),1,0),"No values",,-1)