Help with Cell fornula or format

[Fundman]

We use Office Excel 2003- We need to insert data from preset column B in
Spreadsheet 1 to "new column B" in Spreadsheet 2 (downloaded data in Col A,
but new Col B created by us). Thus, the formula you advise for cells in Sheet
2 column B, need to produce the result x=Medium, y=Small and Z=large, based
on naming criteria match.

Sheet 1 Sheet 2
Col A Col B Col A Col B
FundA Large Fund B x
FundB Medium Fund C y
FundC Small Fund A z

 

[Luke M]

=IF('Sheet1'!B1="Large","x",IF('Sheet1'!B1="Medium","y",IF('Sheet1'!B1="Small","z","Some other entry was present")))

Then you can just copy and paste down.

 

[Fundman]

Unfortunately, this did not totally answer the question.

The formula for Sheet2 Col B Rows1-3, needs to look at 2 variables. First,
it has to recognize the "Cell-Fund B in Sheet2 Col A". It then has to look to
match this text to a cell in "Sheet1 Col A" (in this case, the row 2). Once
it finds that match, it needs to insert the result "medium" in Sheet2,
ColumnB, Row 1. Once you suggest the formula, we confirm that it would be
copied and pasted down.

We look forward to your new suggested solution...Thanks again Luke in advance

 

[Max]

Think a simple vlookup should do work fine

Source data assumed in Sheet1, cols A & B

In Sheet2,
Place in B1:
=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0))
Copy down as far as required

If the above vlookup doesn't match/return 100%, that means you've got a data
consistency issue in the fund names in Sheet1/2's col A - ie they don't
exactly match

If so, you could try this fuzzy, heavier duty index/match ..
Place instead in B1, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(A1="","",INDEX(Sheet1!$B$1:$B$100,MATCH(TRUE,ISNUMBER(SEARCH(A1,Sheet1!$A$1:$A$100)),0)))
Copy down as far as required. Adapt the ranges to suit.

 

[Fundman]

Max-

When I inserted the formula you suggested I got FALSE each time. Perhaps I
don't understand what needs to be input in the quotes for the first part of
the argument A1="",""

However, when I move the sheet 1 legend (columns a and b or a total of 6
cells) into bottom of Sheet 2 and just use the function
VLOOKUP ($E39,$E39:$F41,2,0) it seems to
produce the correct text in column b for each of the 3 arguments. ..Any final
thoughts? Thanks again..

 

[Max]

I'm not sure what's happening over there ..

Perhaps a working sample
to illustrate the earlier suggestion would be good?:

http://cjoint.com/?mqxdgOOOpw
Vlookup_example.xls

The suggested expressionresumes that your source data/table is in Sheet1's col A and B
and your lookup values in Sheet2 starts in A1 down

The front IF part of the expression: =IF(A1="","", ..
is simply to check whether the lookup col A is "blank"
If it is, then the expression will return neat looking blanks: ""
So the formula could be copied down beyond existing data in col A (beyond
the last data row in col A) to cater for future data entries

---

 

[Fundman]

Works now...Thanks Max

I'm not sure what's happening over there ..

Perhaps a working sample
to illustrate the earlier suggestion would be good?:

http://cjoint.com/?mqxdgOOOpw
Vlookup_example.xls

The suggested expression:
presumes that your source data/table is in Sheet1's col A and B
and your lookup values in Sheet2 starts in A1 down

The front IF part of the expression: =IF(A1="","", ..
is simply to check whether the lookup col A is "blank"
If it is, then the expression will return neat looking blanks: ""
So the formula could be copied down beyond existing data in col A (beyond
the last data row in col A) to cater for future data entries

 

[Max]

Glad that cleared it up for you.