Combining VLOOKUP and IF to pull data from an entire row

Copper Contributor

Hi,

 

I am working with a data set that looks at the funding sources at an organization. There are multiple sheets, and I need data from the main sheet to automatically go to certain sheets based on set criteria. I think combining the VLOOKUP and IF functions matches what I'm trying to do, but I keep receiving all sorts of errors.

 

I am attaching a sample datasheet to show the variables I am working with. It is a bit of a confusing task, so I've outlined exactly what I need to do:

 

There is an "All Proposals" work sheet. In it, we document funding from internal and external sources. We also document whether the proposal received funding or not. These are the two key variables that we have separate work sheets for. The work sheets are:

  • "External Proposals" (for all external proposals submitted)
  • "External Funded" (only external proposals that received funding)
  • "Internal Proposals" (all internal proposals submitted)
  • "Internal Funded" (only internal proposals that received funding)

In these separate worksheets, I need a formula for each cell so that the entire proposal row will be pulled in IF the funding source is (internal or external for the respective work sheet) and IF funding was received (only for the funded work sheets). This row includes other information about the proposals (such as who is running the research study, when the project will be taking place, who exactly is funding it, etc). 

 

I would appreciate the help!

6 Replies

Hi Meriam,

 

You can try this.

(for all external proposals submitted)

=IF('All Proposals'!$B2="External",HLOOKUP('External Proposals'!B$1,'All Proposals'!$B$1:$S$10,$A2,0),"")

copy the formula in all cells

 

(only external proposals that received funding)

=IF('All Proposals'!$B2="External",IF('All Proposals'!$C2="yes",HLOOKUP('External Funded'!B$1,'All Proposals'!$B$1:$S$10,$A2,0),""))

copy the formula in all cells

 

 

for internal

Just replace the word"External" with "Internal".

 

 

 

i have inserted i col A as ROW ID in all sheets for simplicity, you can go with manual row numbers in formula.

Thank you for the response! I copied the formula you provided, and adjusted it in the "External Funded" spreadsheet. It returned an empty cell, though, and I'm not sure why. I extended the formula into the entire first row and nothing came up.

 

Here what the actual spread sheet looks like, and the formula I used. Maybe I referenced the wrong cells?

 

Untitled.pngexternal funded formula.png

 

Looking at what you are doing I wonder why you want to have 5 separate worksheets when everything you want is already on one. It would be simpler just to use filters on your "All Proposals" worksheet.  By choosing the appropriate filters on the TypeofFunding and Funded columns you get a sheet with exactly what you want. 

Hi John,

 

We previously made these separate sheets because we regularly report these lists of grants and their amounts, and it is more convenient to have them as separate sheets for this purpose. 

 

We also have graphs that are linked to these sheets that show the total requested and total funded amounts. Even so, it might be less tedious to have a "Total" cell for each condition on the main spreadsheet and then link this entry to the graphs. If there is no direct reference formula for entire rows on separate sheets, it might be easier to do it this way.

 

Thank you

Hi Meriam,
could you please share the worksheet for better understanding of what you have missed.