Vlookup exact match is not found-CodePudding

I am trying to run a Vlookup to find the account name related to the ID. I have checked the format on both tabs, and it's the same, nevertheless the Vlookup is not working even if the value exists in both tabs.

This is my formula

VLOOKUP(L24,'Zuora Invoices 02/05/2022'!B:D,1,false)

Basically,the cell L24 would be the ID in sheet #1 and in the sheet #2 "Zuora invoices I have column D with the same ID and the column B with the corresponding account name.

With control F I am able to find the exact same value in the sheet #2 but the Vlookup result is #NA. If I try VLOOKUP(L24,'Zuora Invoices 02/05/2022'!D:D,1,false) then the Vlookup finds the ID value.

How can I make it work, so I can find the account name by comparing the account IDs?

Best

CodePudding user response：

So vlookup() will be looking for the ID in Column B of the data range B:D that you give it.

If the ID is in column D then it will not work. vlookup works from left to right only.

You will need match() to find the position in column D and index() to find the result. Like so:

=index(B:B,match(L24,Zuora Invoices 02/05/2022'!D:D,0))

Edit the column for index as needed, I assumed col B.

CodePudding user response：

use:

=VLOOKUP(L24, {'Zuora Invoices 02/05/2022'!D:D, 'Zuora Invoices 02/05/2022'!B:B}, 2, 0)