Using IF and VLOOKUP for range of cells
Hello,
I am really new to excel and playing around with a calculator of sorts. What I am trying to do is autofill a specific cell (C2) of a calculator. I made a separate table on the same sheet that has 6 columns, M2:R:30. I thought that if I could place an "x" in any cell of R2:30, I could have a forumla look for the x and then autofill C2 with data from the cooresponding row. So for example place "x" in R2 and the data from M2 gets populated in C2. This is what I have so far. The problem I have is that it works by only putting the data from M2 into C2, no matter where I place the "x" in R. =IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP and failed. I have 4 separate cells that I would like to do this for, C2 cooresponds to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30. Once I get the initial formula correct I should be able to progress. Thanks a lot and I hope what I wrote is not to confusing. Just thought of an easier way to ask. If I place an "x" in any cell of column R that would return the data in the same row of column M to C2. |
Using IF and VLOOKUP for range of cells
On Wednesday, September 26, 2012 2:37:37 PM UTC-7, JamesTris wrote:
Hello, I am really new to excel and playing around with a calculator of sorts. What I am trying to do is autofill a specific cell (C2) of a calculator. I made a separate table on the same sheet that has 6 columns, M2:R:30. I thought that if I could place an "x" in any cell of R2:30, I could have a forumla look for the x and then autofill C2 with data from the cooresponding row. So for example place "x" in R2 and the data from M2 gets populated in C2. This is what I have so far. The problem I have is that it works by only putting the data from M2 into C2, no matter where I place the "x" in R. =IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP and failed. I have 4 separate cells that I would like to do this for, C2 cooresponds to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30. Once I get the initial formula correct I should be able to progress. Thanks a lot and I hope what I wrote is not to confusing. Just thought of an easier way to ask. If I place an "x" in any cell of column R that would return the data in the same row of column M to C2. -- JamesTris Hi JamesTris, Without a full grasp of your sheet layout I'm guessing you want some variation of this Arra-Entered VLookup formula. J2 in this formula would be the "x" on your sheet, the lookup value. A30:E49 is the table array from which you want to extract multiple cell data. {2,3,4,5} are the columns to the right of the lookup value cell that you want returned. The 0 will return an exact match. And the iserror will return blank cells if no match is achieved. =IF(ISERROR(VLOOKUP(J2,A30:E49,{2,3,4,5},0)),"",VL OOKUP(J2,A30:E49,{2,3,4,5},0)) Entering can be a bit tricky but with this formula you would select four cells in the same row across four columns and while still selected (highlighted) click in the formula bar and enter the formula. Once the formula is entered DO NOT HIT ENTER! You now hold down thr Ctrl and the Shift keys and now hit Enter. This is called Array Enter. Excel will encase the formula (which will show up in all four cells) with { } brackets. The data returned will show up in each of the four cells representing the info in the second, third, fourth and fifth columns of the table. If you need to make changes to the formula, select ALL FOUR of the cells as you did to enter the formula and make the changes to the formula shown in the formula bar. Ctrl + Shift + Enter to activate the formulas and notice the { } will reappear around the formulas. So, while my example looks like this {2,3,4,5}, yours if I understand correctly will have 29 colums {2,3,...28,29} I've never done that many but it will probably work ok. Good luck. egards, Howard |
Using IF and VLOOKUP for range of cells
On Wednesday, September 26, 2012 2:37:37 PM UTC-7, JamesTris wrote:
Hello, I am really new to excel and playing around with a calculator of sorts. What I am trying to do is autofill a specific cell (C2) of a calculator. I made a separate table on the same sheet that has 6 columns, M2:R:30. I thought that if I could place an "x" in any cell of R2:30, I could have a forumla look for the x and then autofill C2 with data from the cooresponding row. So for example place "x" in R2 and the data from M2 gets populated in C2. This is what I have so far. The problem I have is that it works by only putting the data from M2 into C2, no matter where I place the "x" in R. =IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP and failed. I have 4 separate cells that I would like to do this for, C2 cooresponds to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30. Once I get the initial formula correct I should be able to progress. Thanks a lot and I hope what I wrote is not to confusing. Just thought of an easier way to ask. If I place an "x" in any cell of column R that would return the data in the same row of column M to C2. -- JamesTris |
Using IF and VLOOKUP for range of cells
On Wednesday, September 26, 2012 2:37:37 PM UTC-7, JamesTris wrote:
Hello, I am really new to excel and playing around with a calculator of sorts. What I am trying to do is autofill a specific cell (C2) of a calculator. I made a separate table on the same sheet that has 6 columns, M2:R:30. I thought that if I could place an "x" in any cell of R2:30, I could have a forumla look for the x and then autofill C2 with data from the cooresponding row. So for example place "x" in R2 and the data from M2 gets populated in C2. This is what I have so far. The problem I have is that it works by only putting the data from M2 into C2, no matter where I place the "x" in R. =IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP and failed. I have 4 separate cells that I would like to do this for, C2 cooresponds to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30. Once I get the initial formula correct I should be able to progress. Thanks a lot and I hope what I wrote is not to confusing. Just thought of an easier way to ask. If I place an "x" in any cell of column R that would return the data in the same row of column M to C2. -- JamesTris Hi again JamesTris, After I sent my post I set about to try to set up a working sample sheet the best I could from your post. I got hopelessly lost on how the sheet should look and what needs to happen and where... etc. I would be glad to look at an example worksheet and give it a shot if you want. I'm at Regards, Howard |
Using IF and VLOOKUP for range of cells
Hi James,
Am Wed, 26 Sep 2012 21:37:37 +0000 schrieb JamesTris: I thought that if I could place an "x" in any cell of R2:30, I could have a forumla look for the x and then autofill C2 with data from the cooresponding row. So for example place "x" in R2 and the data from M2 gets populated in C2. This is what I have so far. The problem I have is that it works by only putting the data from M2 into C2, no matter where I place the "x" in R. I have 4 separate cells that I would like to do this for, C2 cooresponds to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30. Once I get the initial formula correct I should be able to progress. in C2 try: =INDEX(M2:M30,MATCH("x",$R$2:$R$30,0)) in D2: =INDEX(N2:N30,MATCH("x",$R$2:$R$30,0)) and so on Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Quote:
Quote:
|
All times are GMT +1. The time now is 02:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com