formula better than vlookup for this?

    I'd like to write a formula that will give a given result, if a name from one column is within a given array. I can get it to work with an if formula and vlookup, but I have to lookup each individual name.


    For example, if either smith, johnson, or davis are within a column of names, then I would like a value from the second column associated with the name to appear in another cell. As it stands, I have to do a search for smith, then johnson, then davis.


    Also, I'd like it to be heirarchal, in that even if johnson is in the list [regardless of if he's first or not], and davis is in it, also, that davis' value will show up.


    Thanks for all your help.


    dh


    I added a sample .xls - I would like to search column d for any name in array a2:b4, and then put the number from the second column into f2.

    Why can't a simple vlookup work in this scenario.
    IF the name is there lookup up the value next to it.
    IS it that the name appears many times and you want to calculate a total based on that name. That would be a sumif() statement.


    =VLOOKUP(D2,A:B,2,FALSE)


    This is what i put in your column E.

    You cannot do an If statement on an entire column, So essentially what you would have to do is an IF statement on every single cell in that column, then if the statement was true that a particular name exist then give the vlookup of that name. In order to do multiple names if you would have to do If(or(name1,exist),nam2,Exist,Name3 exist etc.


    As you can see it gets a little complicated. I think a macro search may be the best thing for this to work.


    Other than that i am out of options or ideas.

    I was afraid of that, I know nothing about macros, and would like to be able to change a name in column a, without having to do a replace all "Davis" with "Jones". Anyone else have any ideas on how I can do this, or is willing to help me learn macros enough to make this happen?


    thanks,


    dh

    Thanks for your help. I might be able to make that work with a match function, or something. I still need to be able to have any number appear in one given cell, though.


    dh

    try INDEX/MATCH function


    =INDEX($A$2:$B$4,MATCH($D5,$A$2:$A$4),2)


    You can even use Match again for the Col_num (2), if your column has a label.


    INDEX/MATCH is much better than VLOOKUP/HLOOKUP: in the former, the lookup value need not to be on the first column/row (as in VLOOKUP/HLOOKUP).


    Hope this helps...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login