Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup data in the list
Hi All,
I have a list as shown bellow, I want to pick up the date correcponding to the value on cell A1 and the value in B2 or B3. So it is some thing like this =Vlookup(a2,a:b[b2+1] or[b+2] {this is just a vierd example} ColumnA ColumnB ID Name 1 12082A IN-tuition 2 Ms. Gayle 3 Gayle Theresa 4 Sofiane 5 12082B tuition 6 Ms. Gayle 7 12298A Language Institute 8 Senouci Bereksi 9 AOUL 10 Bereksi Kindly Help ASAP, As I am Stuck in my assignment. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup data in the list
Hi, Manish. The better way, of course, is to have the ID in col A, the
company name in B, the individual name in C and so forth; then you can use VLOOKUP and specify the column you want depending on which datum you need to pull for your ID. But if for some reason that option isn't open to you, then what occurs to me is to use MATCH and INDIRECT instead of VLOOKUP. Like this: 1) In col A you have a list of IDs. In col B you have various data associated with each ID, eg the company name in the same row as the ID and an individual's name in the row following. I notice some of your sample data have only those two fields, and others have more; the following method will work with that, but you'll be able to look up only the first two fields, nothing after that. 2) You have an ID you want to look up, say "12082B" 3) Your first formula, then, is =MATCH("12082B",A:A,0). MATCH returns the row number in which 12082B was found, or an error if it isn't in the list. In this case, that formula returns the value 5. Let's pretend that formula is in I2. 4) But you don't want B5 ("tuition"), you want B6 ("Ms. Gayle"). So you use the INDIRECT function, like this: =INDIRECT("B"&I2+1). I2 has the ID's now number in it; this fomula adds one to that row number, tacks it on to B and uses INDIRECT to look up B6. If you need to have the data laid out as below and you want to look up one of the values that is further down, such as "Bereksi" (which is 3 rows down from its ID instead of 0 or 1), you're out of luck; unless you fill in the missing rows for ALL your IDs, your formula will end up looking up the wrong field in some cases. --- "Manish Seth" wrote: I have a list as shown bellow, I want to pick up the date correcponding to the value on cell A1 and the value in B2 or B3. So it is some thing like this =Vlookup(a2,a:b[b2+1] or[b+2] {this is just a vierd example} ColumnA ColumnB ID Name 1 12082A IN-tuition 2 Ms. Gayle 3 Gayle Theresa 4 Sofiane 5 12082B tuition 6 Ms. Gayle 7 12298A Language Institute 8 Senouci Bereksi 9 AOUL 10 Bereksi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and average data in an unsorted list | Excel Worksheet Functions | |||
Return a list of data, rather than just one value for a lookup | Excel Discussion (Misc queries) | |||
lookup? list data into summary table | Excel Worksheet Functions | |||
Lookup with two variable data list cells | Excel Worksheet Functions | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions |