Lookup heirarchy Value in Multiple Columns and Return Value from Column

[venkyzrocks]

Struggling with an Index Match on this lookup since if need to return the lowest level child

Lookup to look value "Miami" (dynamic) in Multiple columns lets says column Column1,Column2,Column3 and
then return lookup value from Lookup columns starting the search from the last column3 in this case and
then going backwards till a value is found. Attaching the excel sheet as well.

Excel.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Value	Return Value Cell (Return last Value)		Column	Column2	Column3		Lookup Column1	Lookup Column2	Lookup Column3
2	Miami	R1 - Since R3,R2 are empty		New1	Miami	Rera		R1
3	Miami	R2- Since R3 is empty		New2	Abc	Miami		R1	R2
4	Miami	R3		New1	Miami	Rera		R1	R2	R3
5
Sheet1

 

[Special-K99]

Not sure I understand what's being asked for.
Isn't this just find the last non blank in columns H to J?
In which case

Try
=INDEX(A2:J2,SUMPRODUCT(MAX((H2:J2<>"")*COLUMN(H2:J2)),1))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm not sure what, if anything, columns D, E & F have to do with it? If those columns are relevant, please explain in what way.
Otherwise, is this what you want?

24 02 05.xlsm
	B	C				G	H	I	J
1	Return Value Cell (Return last Value)						Lookup Column1	Lookup Column2	Lookup Column3
2	R1						R1
3	R2						R1	R2
4	R3						R1	R2	R3
Lookup Last

Cell Formulas
Range	Formula
B2:B4	B2	=LOOKUP("zzz",H2:J2)

 

[venkyzrocks]

Aplogies, the value $A2 looks up in row D,E,F and returns th first non blank value (starting from J, then I, then H). Needs Iindex Match, not just lookup. Hope this helps.

Equivalent of this in lookup terms would be =vlookup($A2, $D:$F (But where the value lies is uncertain), Return (non;blank in reverse order), false)

 

[venkyzrocks]

Any help here?

 

[Peter_SSs]

the value $A2 looks up in row D,E,F and returns th first non blank value (starting from J, then I, then H).

I don't understand that. Which of the answers in my post are incorrect and what should those answers be instead, and why?

 

[dermie_72]

instead of an index match you could use this formula instead. When testing against your data it returned the correct value. If you're expecting something else, please confirm the expected output

In Cell B2
=IF(COUNTIF(D2:F2,A2)<>0,OFFSET(J2,0,-COUNTBLANK(H2:J2)),"")

 

[Special-K99]

First of all D, E, F are columns not Rows.
Secondly as stated by Peter_SSs, what is the purpose of columns D E and F ?
In regards to the results you have in column B, columns D, E, and F are irrelevant to obtaining those results.
ie
If you deleted columns D, E, and F you could still get the same results in B just by looking at H, I, J.
So what purpose does D, E and F serve?