Excel question

MiCr0

hi,
i need to figure this out for my own sanity if nothing more

i have a column of titles
i have a column of values matching the titles

eg
ann 1
barry 2
conor 3
david 4

i need to have a cell setup so if i enter david (say into cell a4) it puts 4 into c4

any ideas?


soln should be something like
=IF(D29=Sheet6!A3:A224,E29=Sheet6!E3:E224,E29="unknown")
but it keeps returning false

MiCr0

nearer a solution

=IF(Sheet6!A3:A224,Sheet6!E3:E224,"unknown")

it matches column a's entry
but i can't get it to pick the correct column e entry

The_Bullman

not too sure what you are looking for but don't if statements go someting like

if(cell number=value,(then)this,(or)that)

hard for me to explain

if(a4=10,"is big","something else")

this means is cell a4 displays 10 then the cell that the if statement is in will display "is big", if the value isn't 10 then it will display "something else"

(without quotes)

MiCr0

yea,
thats what i have

=IF
(D29=Sheet2!A3:A224,
"blah",
"other blah")

the problem is getting "blah" to return the value of the corrosponding value to where d29 was the same as the entry in the a column

Wolff

What you need is vlookup me lad !

The_Bullman

if(d29=a3:a224,"bla","other bla")


ok this has probably gone way over my head, and in which case i am sorry, but don't you have cell d29 equal to a range or values from cell a3 to a224. now i don't know excel very well but is this possible. maybe there sould be only one value not 221 values

bruce wayne

you need a vlookup formula.

basically on the sheet where when you enter the david in one column and you want the corresponding associated value from the other sheet in this case the number 4. then in the field where you want the 4 to appear you need to enter the vlookup formula.

again for example
sheet 1 contains the two columns with the initial data (A1:D4) four rows, and two columns

and
sheet 2 contains the where you want to enter the key word "david" into field A1 and get a corrisponding value in A2

the forumla should look like =vlookup(A1,sheet1!A1:D4,2,false)

or in english lookup the data contained in A1, in the range of date on sheet 1 in fields A1 to D4, and when you find it return the value in the second field (i.e. the field next to the common data).

the false and true part at the end as far as I can remember is false for an exact match and true for a near match.

couple of points to note if using. If both the sheets are in the same workbook you will need to absolute the range by putting in $ before and after the numeric. And if the data range where the info is being looked up contains two or more of the same data (i.e. two fields with the name david) it will return the first corrisponding value it comes across - from the top down.

Hope this helped.....sorry its a bit long winded

Gordon

But it has smiley faces so that's OK : D

MiCr0

info here
i'm trying to get the currency to match from sheet 2 and then take the euro rate

ta

rename .xls

Mercury_Tilt

This post has been deleted.

MiCr0

cheers all (esp Mercury_Tilt)
you are the WIN!