How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Financial Specialist
Category: Financial Software
Satisfied Customers: 31127
Experience:  IT professional, resolving issues with financial programs.
32989067
Type Your Financial Software Question Here...
Richard is online now
A new question is answered every 9 seconds

I cannot make a hlookup work in excel 2013

Customer Question

I cannot make a hlookup work in excel 2013
Submitted: 1 year ago.
Category: Financial Software
Expert:  Richard replied 1 year ago.

Hi there and welcome

Can I look at your file please to see the issue with it.

Attach it please to the question and I can check the issue

Thank you

Customer: replied 1 year ago.
I will have to edit out some names
Expert:  Richard replied 1 year ago.

no problem, do this please and let me see the file so I can check the issue

Expert:  Richard replied 1 year ago.

I work online here but can connect to your computer if you wish?

Customer: replied 1 year ago.
Although I prefer telephone support here is the file
Customer: replied 1 year ago.
Also I will use the "Partners" tab for a mail merge incorporating the data for each partner in the letter
Expert:  Richard replied 1 year ago.

can you tell me in your own words what you want the cell to show please

use Cell references in your explanation please

Customer: replied 1 year ago.
in cells e2,f2,g2,h2 & i2 using the InvNum in cell d2 (all on the Partners tab) to look up the values in cells cdegh27 for the partner with the same InvNum partner number all in the calcs tab for the first partner the common number is ***** in cell C27
Expert:  Richard replied 1 year ago.

ok, so which value should E2 be returning?

Customer: replied 1 year ago.
Cell c27 Cell D27 Cell E27 Cell G27 Cell H27........................... ON calc
1080 (31,109.68) 76,771.00 - (4,294.43) (49,955.75)
Cell E2 Cell F2 Cell G2 H2............................all on PartnersAnd then it needs to work for the next partner on Partners looking along row 27 on calcs for the next Partner number (1083) etc
Customer: replied 1 year ago.
Sorry the formatting did not work shall I send it as a spreadsheet?
Customer: replied 1 year ago.
this should be clearer
Expert:  Richard replied 1 year ago.

ok I see, so in effect it should always return the total for the invoice number

Expert:  Richard replied 1 year ago.

give me a few minutes and ill make the formula

Customer: replied 1 year ago.
Richard,It seems this is much more difficult than I thought. I can get each partners' information from the calcs sheet and paste it in the Partners Sheet. I had hoped that using the hlookup this would not be necessary and would certainly be a time saver when the data changes in the future. Do you think you can solve this in an hour from now?
Customer: replied 1 year ago.
The inv stands for investor not invoice) - for each investor there will be four different numbers looked up from calcs using the inv number to link the two sheets
Expert:  Richard replied 1 year ago.

ok, the lookups return corresponding data, which this is not, as its not in the same row as the criteria

Expert:  Richard replied 1 year ago.

If for example the total was in the same row as invoice number it would be easy but as its not I need to do a long if statement to match this

Expert:  Richard replied 1 year ago.

then repeat for each field, then you would need to use these statements, but the total would need to remain in the same cell each time

Expert:  Richard replied 1 year ago.

will this be the case?

Expert:  Richard replied 1 year ago.

so for example something like

=IF(D2=1080,SUM(Calcs!D27),0)

Expert:  Richard replied 1 year ago.

Then you need to do it for each combination thats possible

unless you can change the invoices layout so the total is to the right of the invoice number, is this possible when the sheets are created?

Customer: replied 1 year ago.
I regret I do not understand what you have written. You make repeated references to Invoices and Totals whereas all I need is for a cell on the Partners sheet to be filled with the value FOR THAT PARTNER (Indentified by a partner no) from the four values each partner has in calcs. i.e. in the calcs sheet on row 27 Find partner no 1080 and give me the value in the cell to its immediate right and put that value in E2 on Partners, then in F2 (Partners) Find partner no 1080 on row 27 (calcs) and give me the value in the cell which is the second cell after the investor no etcPlease let me know as soon as possible if this can be done. If not I will have to resort to a cut and paste operation each time.
Expert:  Richard replied 1 year ago.

yes as we are using the invnum (I assumed was invoice number) as the reference.

And you want to display the Totals for example (31,109.68) the total of Contribs within the 1080 area

I am sorry if I got the wording wrong, but the understanding is the same

Regardless, it can be done but it is with an If Statement and you would need to do an if statement for each invoice number.

So I asked just now, as the if statement is no quicker then copying and pasting, if the formate of the totals can be changed so for example 1080 has the totals in the cells to the right of it as then we can use a lookup.

Else you would be best to copy and paste it.

Customer: replied 1 year ago.
But the totals are to the right of each investor number all on row 27 (there is a blank cell between cells 1 & 2 AND 3 & 4 but that can be eliminated if it causes a problem
Expert:  Richard replied 1 year ago.

ye, but its only one value, you would need that row, listed together with all the other rows and then its possible

So for example

1080 (31,109.68) 76,771.00 - (4,294.43) (49,955.75)

1083 (77,774.15) 191,923.00 - (11,365.62) (125,514.47)

1421 (207,397.81) 511,799.00 - (28,303.57) (332,704.76)

Then a lookup works as it has the data to lookup though, can the data be made that way? I can make it for this sheet, but you have other sheets like this correct?

Customer: replied 1 year ago.
Using the calcs sheet? Please let me see what you do.
Expert:  Richard replied 1 year ago.

have a look at this sheet please, you see what I did in E2, using the Sheet 2 to store the rows

http://wikisend.com/download/320156/Just+answer+excel (1).xlsx

Customer: replied 1 year ago.
I assume the data was a copy and paste from calc to sheet 2? and then you used a vlookup in partners? If that's the case then I may as well copy and paste directly from calc to partners.
Expert:  Richard replied 1 year ago.

correct, as that's the only way to get it to do the lookup, which is why I was indicating before, if you cannot get the data to be generated in this fashion you are best to copy and paste it

Customer: replied 1 year ago.
Sorry to have wasted our time
Expert:  Richard replied 1 year ago.

well you needed to see if it was possible, and once I understood how the data was constructed we could see this. The time was needed to understand the file you had, it is always the case. I do not believe it was wasted, just required to see if it was possible,

If you can get a change to the way the data is constructed for the file (likely comes from a program?) then it can be done. And you got to at least see how lookups work

If you would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service by clicking one of the stars at the top of the screen then submit, that would be greatly appreciated.

Thank you

Related Financial Software Questions