Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Help with Excel formula w/ multiple references...

Nov 3, 2005 10:17PM PST

I need some help with a formula. I have two Excel workbooks. The first is a list of store numbers in column 1 with a percentage for each store in column 2. The second sheet is a financial report that I am importing data into. One of the cells I am importing is the store number.

What I am trying to do is have Excel automatically reference the percentage from sheet 1 by comparing the imported store number on sheet 2 with the store number list on sheet 1.

Here's the formula I'm trying to build on. This formula is in workbook 2. Workbook 1 A3 is the store number, workbook1 C3 is the percentage, and workbook2 E4 is the imported store number.

=IF(E4=[workbook1.xls]Sheet1!$A$3,[workbook1.xls]Sheet1!$C$3,0)

The problem I'm running into is that workbook 1 has about 30 entries I need to automatically scan through.

Can anyone help?

Thanks!!!

Ryan

Discussion is locked

- Collapse -
Re: Excel lookup
Nov 3, 2005 10:53PM PST

Ryan,

The function to use is 'vertical lookup' (VLOOKUP). There's info in Excel's help function, if the function wizard isn't clear enough. Or see http://www.ozgrid.com/Excel/excel-vlookup-formula.htm (just the first google hit for excel vlookup, you'll find many more if you go and look for it).

Hope this helps.


Kees

- Collapse -
Thanks, but a problem...
Nov 3, 2005 11:58PM PST

Kees, that works great! But I do have a problem though...

I actually have two columns of numbers on WB2, columns C and D. I'm trying to get a number from each onto WB1, into E6 and E7. E6=column C and E7=column D.

E6 works fine with this forumla:

=VLOOKUP($E$4,[WB2.xls]Sheet1!$A$2:$C$200,3)

When I type the following formula into E7:

=VLOOKUP($E$4,[WB2.xls]Sheet1!$A$2:$C$200,4)

I get a #REF! From what I gathered on the site you linked to, the final digit is the number of columns to shift, right? The third column on WB2 is 5% and the 4th is 2%. I get the five but the error in place of the 2.

Do you have any ideas?

Thanks!
Ryan

- Collapse -
My idea ...
Nov 4, 2005 2:39AM PST

Range $a$2:$c$200 is 3 columns wide. You can ask the third column, but not the fourth. Change the range to $a$2:$d$200 and see what happens.

Kees

- Collapse -
Eureka!!
Nov 4, 2005 4:09AM PST

Brilliant. Thanks a ton! That did it.

Ryan

- Collapse -
Glad I could help, but "brilliant" is too much honor ...
Nov 4, 2005 4:17AM PST

for being able to count to 4 Happy.

- Collapse -
Cancel...
Nov 3, 2005 11:00PM PST

Cancel, I figured it out. Happy

=IF(E4=[workbook2.xls]Sheet1!$B$1,[workbook2.xls]Sheet1!$B$3,IF(E4=[workbook2.xls]Sheet1!$c$1,[workbook2.xls]Sheet1!$c$3,etc etc etc)

Ryan

- Collapse -
excel formula
Nov 3, 2005 11:16PM PST

I need help with a what if formula I am trying to have it do if column a has x info and column b has y info then total the matches? Any ideas?

- Collapse -
Cancel the cancel...
Nov 3, 2005 11:17PM PST

Cancel the cancel, it's limiting me at about 15 lines. Crap. I'll try the suggestion Kees! Thanks!!

Ryan