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