SOLVED

Question with IF AND - How do I pull the return information from a different spreadsheet?

Copper Contributor
I'm trying to do a lookup using an ID number and the year and return info from another spreadsheet.  Can this be done? and if so , how to I reference the other spreadsheet in the formula?
4 Replies
best response confirmed by allyreckerman (Microsoft)
Solution
Excel is not the smartest at this so we need to help it a little bit.
In the lookup table you can add a helper column as the first column that has this formula: =ID number&year

The lookup formula will be: =VLOOKUP(cellwithID&cellwithyear,TableRange,column number to return)

This way we avoid the if/and statements and instead do a lookup of a value that is IDnumber AND year.

If you have trouble with this send an example spreadsheet and I will write the formula in.

Good Luck!

@DN123 

The easiest way to do that is to point to the relevant range on the other worksheet while creating (or editing) the formula. Excel will automatically create the correct reference.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution
Excel is not the smartest at this so we need to help it a little bit.
In the lookup table you can add a helper column as the first column that has this formula: =ID number&year

The lookup formula will be: =VLOOKUP(cellwithID&cellwithyear,TableRange,column number to return)

This way we avoid the if/and statements and instead do a lookup of a value that is IDnumber AND year.

If you have trouble with this send an example spreadsheet and I will write the formula in.

Good Luck!

View solution in original post