Using IF and VLOOKUP for range of cells

[JamesTris]

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 coul
have a forumla look for the x and then autofill C2 with data from th
cooresponding row. So for example place "x" in R2 and the data from M
gets populated in C2. This is what I have so far. The problem I have i
that it works by only putting the data from M2 into C2, no matter wher
I place the "x" in R.

=IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKU
and failed.

I have 4 separate cells that I would like to do this for, C2 coorespond
to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P230
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 i
the same row of column M to C2

 

[lhkittle]

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 P230.

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.

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)),"",VLOOKUP(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 ofthe 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 asyou 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

 

[lhkittle]

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 P230.

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.

 

[lhkittle]

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 P230.

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.

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 (e-mail address removed)

Regards,
Howard

 

[Claus Busch]

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 P230.
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

 

[JamesTris]

Hi again JamesTris,

After I sent my post I set about to try to set up a working sample shee
the best I could from your post. I got hopelessly lost on how the shee
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 i
you want.
I'm at (e-mail address removed)

Regards,
Howard

Thank you for trying Howard, appreciate it.

Hi James,

Am Wed, 26 Sep 2012 21:37:37 +0000 schrieb JamesTris:
-

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

Thank you so much Claus that worked out great. I really appreciate it