IF STATEMENT/VLOOKUP/???????
Posted by John on April 07, 2001 9:04 PM
I have a list where there are four columns on Sheet 1.
A=Name, B=Phone, C=Company X Employee ID, D=Company Y Employee ID
Example Below:
Name Phone Company X Company Y
Mark 123-456-7890 888 222
John 103-555-0000 555 777
Then on Sheet 2, I have some cells with various data:
J1=Name
J2=Company Name which could be either Company X or Y.
I would like to create a formula in J3 that says look at J1, then with that employees name pick up the name of the Company in J2, then using that tell me the code of that employee under the respective column of company name.
For example:
J1=Mark
J2= Company Y
J3 then equals 222.
I tried and tried working on this formula for several years as I have run into this problem before, but never found a solution myself, yet I do believe there to be a simple one with the help of a more advanced Excel user than myself. Thanks in advance for anyone taking the time to read this and/or provide a solution.
Posted by Dave Hawley on April 07, 2001 10:02 PM
Hi John
Let's see if I have understood you.
Name the Column with the heading "CompanyX" as "CompanyX" name the other Column "CompanyY".
On your other sheet in J2 put:
=VLOOKUP(J1,Sheet1!A1:D500,COLUMN(INDIRECT(J2)),FALSE)
Providing that you have typed "CompanyX" or "CompanyY" in cell J2, it will work.
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on April 08, 2001 2:19 AM
John
If I understood it the right way, you can do the following.
On sheet1, select all the cells (excepting the labels Name, Phone, etc.) and name the selected range SOURCE).
Note that from the point of view of VLOOKUP, the Company X is column 3, the Company Y is column 4.
On sheet 2,
in J3 enter: =VLOOKUP(J1,SOURCE,IF(J2="Company X",3,4),0)
Caveat. If the value of J1 (a name) is not unique (that is, distinct) in SOURCE, you will get an associated value for the first occurrence.
Hope this is what you wanted.
Aladin
