Excel Key in (look up function)

Contributor

Hi Excel Community,

 

Pls assist formula for below.

In column D, i should key in the no. code(column B), column A should reflect on the column D, if i key in the no. code.

 

Ex: i put no. 2 in column D, then Bacolod should reflect on the column D. 

 

Julius575_0-1663813014687.png

 

6 Replies

@Julius575 

In E2:

=XLOOKUP(D2,$B$2:$B$23,$A$2:$A$23,"")

Fill down.

@Hans Vogelaar , just tried, per below figure, nothing reflects at E2.

 

Julius575_0-1663834114378.png

 

@Julius575 

OK, that means you don't have Microsoft 365 or Office 2021, but an older version. Change the formula to

 

=IFERROR(INDEX($A$2:$A$23, MATCH(D2, $B$2:$B$23, 0), "")

 

and fill down.

@Hans Vogelaar , got it!

another thing, column E should reflect on the route provided, this should be CMX.

example, when i in input column D(MNL-CGY), CMX should be reflected in column E.

 

Julius575_0-1663895150540.png

 

same with the other rows base on the route

@Julius575 

See my previous reply.