How to change the lookup column in vlookup-CodePudding

I'm using the formula

=VLOOKUP(R3,'Payment Term'!A2:B9,2,0)

and my lookup table is

So, now when I enter 1 in R3, I get 'Net' but I want the opposite behavior, I want to enter 'Net' and get the id but I am not able to find a way to do it. Is this even possible

CodePudding user response：

You can use =XLOOKUP

=XLOOKUP(R3,'Payment Term'!B2:B9,'Payment Term'!A2:A9,"not found",0)

A quite new formula that can be used instead of the index/match-combo

CodePudding user response：

You have to use INDEX/MATCH instaed of VLOOKUP() because vlookup always lookup value in first column. Try-

=INDEX('Payment Term'!$A$2:$A$9,MATCH(R3,'Payment Term'!$B$2:$B$9,0))