CODEWITHSUNDEEP
excel-formula
Hari
Hari

Reputation: 1

Excel formula to find final value for each row

Excel formula to find final value for each row.

For Example check for Cell B1 match with Column A, If yes assign the adjacent value from Column B and re run the check on column A until no match found. Get the final matched value.

Vlookup gave me first value but I need to find final value.

Sample Excel screen shot

Upvotes: 0

Views: 83

Answers (2)

P.b
P.b

Reputation: 11578

I think you mean recursive lookup; this could be achieved in Office 365:

=LET(a,A2:A9,
     b,B2:B9,
     MAP(a, b, LAMBDA(u, v,
         REDUCE(v, UNIQUE(b), LAMBDA(x, y,
XLOOKUP(x,a,b,x))))))

This starts with the start values in column A and returns the value found in column B in that row; next it will lookup that value in column A and return the value of B in a loop and returns the last result if no new matches are found, else it returns the value for the new match. This is looped the number of times of the unique values in column B.

enter image description here

Anothere example can be found here (bottom part of answer).

Upvotes: 0

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27338

Using XMATCH() & ISNA()

enter image description here

• Formula used in cell C2

=IF(ISNA(XMATCH(B2:B9,A:A)),B2:B9,"f")

The above formula will Spill hence no need to drag, but if you are not using MS365 then use MATCH()

enter image description here

=IF(ISNA(MATCH(B2,A:A,0)),B2,"f")

Upvotes: 0

Related Questions