Trouble with Index and Match

[Permagrin]

Hello all.

main.jpg

clients.jpg

The upper image is from the main tab and the lower is clients.

I need column d to show the serial number from the corresponding location from the clients tab.

The formula I have for d23 is:

Please Login or Register  to view this content.

As you can see D23 is returning a #N/A while the rest are shifted 1 down from where they should be.

I cannot use copy/paste from clients to main since there are gaps in the numbers on the clients tab. If the cell could just be blank if there is no corresponding location on the clients tab that would be great too.

Any help would be greatly appreciated.

Thanks!

[mehmetcik]

Your formula seems Ok.

Maybe

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[kersplash]

I set up a spreadsheet as per the original post and it works fine for me.

[FlameRetired]

Permagrin, by any chance are either of the columns A calculated numbers formatted A00 and not text? If so ... and as MATCH is defaulting to approximate match ... I would look for a floating point error. It's just a thought.

[leelnich]

Hi all- Are you sure the formula looking for A23 is on ROW 23, because it seems you using the previous row's lookup value.

[Permagrin]

I found the problem.

The Clients data was exported from Sharepoint. It was all formatted as text with trailing spaces. Once I fixed that everything worked they way it should. Thanks for the suggestion FlameRetired.

Thanks to everyone else as well for your time.

Marking as resolved.

[FlameRetired]

You're welcome. Thanks for letting us know, marking this Solved and the added rep.