Lookup field based on two variables

[00pumpkin]

Hello All,

I'm trying to create a template that I can use to take an income statement out of a basic QuickBooks export and add some other figures where I see fit.

In the attached excel sheet you will see that I'm trying to pull a data field from the QuickBooks export on the left to the template on the right. The two variables are the heading entry for each field and the corresponding month. I know how to do a vlookup for one month but I want to have a formula that is already setup for any month I have at the top of my template.

Can you please help me figure this out?

Monthly Income Template.xlsx

[Richard Buttrey]

Hi,

If the layout is always the same there's no need for a vlookup, just link the cells directly. e.g. P6: =I6

Otherwise please clarify.

[00pumpkin]

The layout wont always be the same. Months will change, etc. Sometimes only one month will come through, sometimes there will be multiple and a total column.

[Richard Buttrey]

Will the row labels always be the same and will they always be laid out as the example and on the same rows?

Will you always have as many months on the right hand side as the left?

I'm struggling to understand how you use this in practice, what changes and how.

[00pumpkin]

Well, I was thinking about creating a template like the one on the right for the whole year and then just hiding the future months that aren't in use. The row labels will always be the same.

My plan was to have an open tab in excel that I can just drop the data into out of QuickBooks and then have another tab with the template that will pull the data into the specified cells.

But again; I might be dropping in one month, three months, or six months so I cant just have it pull from a predetermined cell. I need it to look up a cell based on the name of the row headings and the month at the top of the column.

Thanks for your help!

[Richard Buttrey]

Hi,

See attached

[00pumpkin]

So with this workbook it looks like the field headings always have to be in the same order/position in order for this formula to work, correct?

[Richard Buttrey]

Hi,

If by field headings you mean the month strings Jan14, Feb14 etc. then no. They can be anywhere on row 2.
If you mean the row labels then as it stands, yes. But only because in post #5 you said the row labels will always be the same. Because of that statement there was no need to build in another MATCH() function to find the 'right' row number since it's said to be always the same.

If the row does change contrary to your original statement then an additional MATCH() function needs to be incorporated.

[00pumpkin]

Sorry, I meant the row headings. They should always be in the same place so I guess there is no need like you said. Thank you for your help!