Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inex and Lookup searches
Using MS Office Professional 2003
Given: Four columns (A-D) titled ID, Name, Job, Job Description Data range is A2:D158 Data is sorted alphabetically by last name: ID Name Job Job Description 502733 Baker,Mark DIR VP MFG 502160 Jones,Debra DIR VP Finance 501565 Michaels,Susan DIR VP MKTG 504729 Smith,David DIR VP Technology I have four names on the same sheet that already appear on the list. They are listed from A:164:A167 in random alphabetical order by last name. I want to search the data range referenced above and populate the ID number in column B and Job Description in column C for my individuals names. Formula for ID: =INDEX(A2:A158,MATCH(A164,$B$2:$B$158,TRUE),1) Formula for Job Description: =LOOKUP(A164,$B$2:$D$158) Problem: The formulas work for cells A164 and A165. Cells A166 and A167 return the ID number and Job Description listed for the individual appearing one row before my individual in the data range. In other words, my individuals information is listed in row 38 and the formula is returning the information for row 37. I dont understand why this is happening or how to resolve it. Help is much appreciated! Cynthia -- Cynthia |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inex and Lookup searches
With the data not in alphabetical order, and since you are not using exact
matches, excel will search down until it finds an approximate value and then go by that data. To fix, I believe all you need to do is change the TRUE in your match statement for ID to a 0 (which will then look for an exact match. 1, 0, and -1 are the choices here). In addition, instead of a LOOKUP for your Job Descriptino, you would want a VLOOKUP: =VLOOKUP(A164,$B$2:$D$158,3,FALSE) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Cynthia" wrote: Using MS Office Professional 2003 Given: Four columns (A-D) titled ID, Name, Job, Job Description Data range is A2:D158 Data is sorted alphabetically by last name: ID Name Job Job Description 502733 Baker,Mark DIR VP MFG 502160 Jones,Debra DIR VP Finance 501565 Michaels,Susan DIR VP MKTG 504729 Smith,David DIR VP Technology I have four names on the same sheet that already appear on the list. They are listed from A:164:A167 in random alphabetical order by last name. I want to search the data range referenced above and populate the ID number in column B and Job Description in column C for my individuals names. Formula for ID: =INDEX(A2:A158,MATCH(A164,$B$2:$B$158,TRUE),1) Formula for Job Description: =LOOKUP(A164,$B$2:$D$158) Problem: The formulas work for cells A164 and A165. Cells A166 and A167 return the ID number and Job Description listed for the individual appearing one row before my individual in the data range. In other words, my individuals information is listed in row 38 and the formula is returning the information for row 37. I dont understand why this is happening or how to resolve it. Help is much appreciated! Cynthia -- Cynthia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
searches in Excel | Excel Discussion (Misc queries) | |||
Pulldown that searches? | Excel Discussion (Misc queries) | |||
Getting valid web searches and avoiding sites that contaminate web searches | New Users to Excel | |||
Workbook searches | Excel Worksheet Functions | |||
Searches | Excel Worksheet Functions |