I'm stumped!
Coworker creates "Sheet A" with invoice numbers
I use Monarch to extract invoice #'s and amounts from another report.
I do a pivot table to compile amounts by invoice. I copy those results to "Sheet Z".
I then do an @vlookup on Sheet A referencing Sheet Z (just the invoice # and amount).
It isn't working unless I go to Sheet Z and type over the invoice # (same number)! All invoice #'s are absolute and in the same font & alignment. Workbook has 10 yrs data. Has worked in past!

like
Posting as :
works at
You are currently posting as works at

Sounds like they’re getting formatted as text instead of number

likesmart

What you select is important when you paste the data back in. It can change the data if you don't paste as values.

Are invoice #’s the left most column of the table your looking up?

like

Yes

Use TRIM(). Might have leading or trailing spaces

like

Okay, I'll try that.

Use Iferror(numbervalue(),trim()) for your lookup value.

like

Sounds like you need to use power query.

like

Have you tried trim to ensure no extra spaces

like

Using "trim" worked! Thanks so much everyone.

like

What error is the formula returning? Have you tried an index match instead? (I don’t use pivots in my role but I think their is some ‘Get Pivot Data’ setting, not sure if that is applicable though.)

The typical #N/A result. I don't know anything about using index.

Is it possible the data got read as text based? Try NumberValue() to convert. I had a to do this recently even though everything told me it should have been reading as a number value.

I will try that!

Type the number 1 in cell and copy that and go to the column on sheet z and select the whole column then go paste>paste special>multiply and then they should both lookup appropriately

Substitute(trim(),” “,””) should remove any spaces or weird stuff

Also could try vlookup(1*account number, invoice number column, whatever column you want to return, false)

Are you copying and pasting as values. Every time I have issues with lookups that seems
To be the problem, I forget that step

Yes I am.

Did you try this &”” ?

Related Posts

like

Why are the benefits at Microsoft Netherlands so bad? It feels like the team behind the benefits never put any work at all. Microsoft

like

Hello Fishes!

I’m close to getting offers from
Thoucentric and Cyient as well for Data Scientist position.

I’m currently a SW Eng+Data Scientist with 11 LPA package 4 YOE. My main goal here is to learn & become better so I can go for better companies + get good enough salary.

Which one would you say is a better company?

like

Anyone recruiting in medical affairs /medical information?

like

Anyone have any recs for Belize? Thinking of going end of April/early May, would be with someone who isn’t scuba certified but wants to learn. General travel recs for Belize welcome as well!! Thanks 🤩

Need advice...showed up to an Airbnb in New Orleans and it’s been double booked and the other guests are here. Airbnb has not been helpful, does anyone know the policy?!

like

Hello

Looking for a 3bhk semi furnished flat for rent in JP nagar and Jayanagar for a family in a society. Would like to connect with any leads

Thanks.

like
like

Any suggestions on stock purchases for long term(10-15 yrs)

like

Negotiating with Amazon and asked for total comp of $160K. Is this a fair offer for a L5 supply chain management role in Cali?

like

I just switched jobs only to find out their processes and software are outdated. I’m afraid that I will lose my skills in the new automation and marketing platforms like HubSpot. I asked if there was a chance to upgrade but it’s all done through corporate. Ily old job was tech savvier but my boss was a terrible micromanager. I’m thinking just stick around for 2-3 years. Maybe.

like

SFO. You have to go outside security to switch terminals. How inefficient! And then you don't have precheck on your second ticket... Seriously?!?

like

How does one get managerial experience without having any prospects of a team working under them?

like

How do you all feel about a Green New Deal? And why?

like

What steps should I take to become a marketing manager in 4 years? I'm currently a coordinator but I'm very goal-oriented. Determined to achieve this!

like

Any Amazon EAs out there? What has been your experience? Is it worth it and is there good mobility from the EA position?

like

Hi all, I gave interview in amdocs recently. After 2 rounds when I am seeing the status on career lage it shows Process ended ?
Does this mean I am rejected ?
Both the rounds went well. Can someone pls tell.

like

Hi everyone I am a new grad and wanted some tips for salary negotiom for the future. I joined a firm and recognize now that their is no upward mobility at all so realize that I will probably move after a year or 2. What are some tips for negotiatig a salary. Also I need likes to dm people so please run them up 🙏.

like

What is the best way to cope with random bystanders teasing you on the street?

like

How are the onsite opportunities in JP morgan ?
Should I not even keep a hope ?

likehelpful

Additional Posts in Excel Genius

I have 4 columns on a sheet: Name (John), Status (open), Priority (high), and Due Date. How I do count how many open, high priority, past due, action items from John?

Pivot keeps changing my dates from m/d/y to D-month. Whyyyy

like

How do you calculate the “rate of change” within excel? I am comparing the number of bugs from one week to another.

like

Do you still use index match? Is it (Always) Possible to switch to xlookup?

like

Is there a way to have a macro paste a value based on an index match? I have a series of investment levels in one column and a series of growth rates in one row. It’s running through a very complex calculator (therefore cannot do a plain sensitivity table) so I am wondering if after it works through the calculator, it can copy the output and paste it in the corresponding investment level/growth rate intersection

like

Hello All,
Excel help please. I’m trying to combine 3 tabs into 1. So on tabs a,b,c I have wire instructions that go to different places, but they all have the same field names/format. However the number of rows for each tab will vary from day to day. I need to make a fourth tab that shows all the rows from a,b,c consecutively. Because the number of rows change, I can’t find the best way to collate all the rows into one table. I can use a macro or lookups or a source formula. Please advise!
Mo

like

I’m trying to consolidated planning of FTEs from weeks to fiscal periods in a new table. If my column heading in the original table is 1, 2, 3 etc and my new table is Period 1, Period 2 etc
What is the most efficient formula to do this? Just doing =sum(Hours!E7:H7) for each period will take forever… thank you!!

like

Hi! Disclaimer: not an excel genius…

I’m trying to use the lookup function to grab data from a worksheet within the same workbook. I want the column to reference the cost code in one worksheet, find the cost code in the other and display the billed to date associated with it. The problem is that it repeats an amount randomly. There will be multiples then a blank cell in one that I know has a cost associated with it.
Formula=LOOKUP(D3,!Sheet!$D:$D,!Sheet!$H:$H)

like

does anyone know what CVS medical excel assessment for finance analyst consist of? I have an interview in a few hours

like

Does anyone here use ASAP Utilites as an Excel add-in? This thing is amazing.

like

Does anyone know if Excel is compatable with Adobe Sign/PDF? I'm trying to automate data entry from Adobe sign/PDF to an Excel sheet. Adobe

like

Anyone know why my wildcards * are not working consistently in sumifs? If I combine multiple sumifs in one formula using + the wildcard only works in some parts. In other parts I need to specify the exact full text which makes my formula lengthier than needed. For example, "app*" isn't picking up "application" and "apple" so I need two separate sumifs to pick up these two.

like

Just wanna say thank you to this group of beautiful people, always willing to help each other reach a solution!

likeuplifting

Is there a way/shortcut, without using a mouse to drag cells down, to fill a range of data following a trend? I want a column with the first day of each month up to a certain date (row number). Thanks

What are your favorite excel macros to make you more efficient at work?

like

Hi all!
I have a file that is full of hyperlinks to different files in another folder.
I moved the location of tbe original fil and hyperlinks stopped working.
Now I also want to move the location of the hyperlinks folder.
Is there a way of fixing the links? (File names are still the same)

like

Anyone know of a formula or how to create a formula that will do the following:

If cell N3 in sheet 1 meets certain criteria it will automatically copy the data in columns B3, C3, D3, F3, G3, H3, I3, J3, and N3 onto spreadsheet 2.

Thank you in advance for any feedback!

like

In VBA when sending an email. How do I change the reply to email?

like

Anyone kind enough to help me start a PE fund model with a waterfall.
Struggling to get started.

like

How TF do I attach the SharePoint version of a slide deck in an email? Every time I try to share, it gives me the link in the email body instead of an actual attachment to open in app. PLEASE AND THANK YOU

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply