|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 9th, 2002, 01:16 PM
|
#1 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Hi.
The lookup functions on excel do not satisfy my requirements. I have two columns then data, like this: Class Type NPols ANN 507 100 ANN 508 123 ANN 552 6903 ANN 548 1234 ANW 507 4520 ANW 508 23520 ANW 510 2420 *** 507 5285 *** 509 2520 *** 510 2343 Now, I want to retrieve the data NPOLS for "*** 507" say so the function would pick out 5285. Any help on this appreciated. I don't want a VBA solution sorry I want to be able to do this on the worksheet if possible. A related problem was the following, I had data like this Year Policy Data1 Data2 1991 Life 2345 42420 Pension 2355 2449 1992 Life 94802 42420 Pension 4295 24249 1993 Life 2420 63729 Pension 24020 74240 etc. and I just couldn't come up with a lookup solution to find Data2 for 1992 Pension say. The required function would give me 24249. Again, I just can't change the labels and I require a lookup solution, not VBA please. Many thanks, RET79 [ This Message was edited by: RET79 on 2002-05-09 12:18 ] |
|
|
|
May 9th, 2002, 01:24 PM
|
#2 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Austin, Texas USA
Posts: 11,654
|
If your data is in A1:C11 use...
{=INDEX(C2:C11,MATCH("ASS507",A2:A11&B2:B11,0))} ...Or, if you're really determined to include the intervening space character... {=INDEX(C2:C11,MATCH("*** 507",A2:A11&" "&B2:B11,0))} Note: These are an array formulas which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. [ This Message was edited by: Mark W. on 2002-05-09 12:27 ] |
|
|
|
|
May 9th, 2002, 01:28 PM
|
#3 | |
|
Board Regular
Join Date: Mar 2002
Posts: 363
|
Quote:
=Vlookup("ASS507",table,4,false)
__________________
It's never too late to learn something new. Ricky |
|
|
|
|
|
May 9th, 2002, 01:28 PM
|
#4 |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
Hi,
Use INDEX and MATCH with a two-way lookup on the MATCH column. Your data table is in A1:C9 In E1: "***" (might want to change this!) In E2: 507 Array enter (!!) the following in your result cell =INDEX(A1:C9,MATCH(E1&E2,A1:A9&B1:B9,0),3) This returned 5285 for me. Bye, Jay |
|
|
|
|
May 9th, 2002, 01:32 PM
|
#5 | |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
Quote:
|
|
|
|
|
|
May 9th, 2002, 01:42 PM
|
#6 | |
|
Board Regular
Join Date: Apr 2002
Location: Kissimmee, Florida
Posts: 384
|
Quote:
Your 1st part of the question can be answered with, Search Criteria is in G1 (ie *** 507) Table is in A1:C11 3 is the 3rd column False only picks up exact match =vlookup(G1,A1:C11,3,False) The 2nd part of your question looks like you need to reformat your table slightly and is then another VLookup. Code:
A B C D E 1 YrPol Year Policy Data1 Data2 2 =B2&C2 1991 Life 2345 42420 3 =B3&C3 1991 Pension 2355 2449 4 =B4&C4 1992 Life 94802 42420 5 =B5&C5 1992 Pension 4295 24249 6 =B6&C6 1993 Life 2420 63729 7 =B7&C7 1993 Pension 24020 74240 Next to retrieve your values Place your query year in G1 Place your Query Type (Pension) in G2 in G3 place you formula =vlookup(G1&G2,A1:E7,5,False) I tested and this works. _________________  [ This Message was edited by: s-o-s on 2002-05-09 13:34 ] |
|
|
|
|
|
May 9th, 2002, 02:08 PM
|
#7 |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Since you don't seem to have duplicate records for class and type (a prerequisite), you can also use:
=OFFSET(C2,SUMPRODUCT((A2:A11=E1)*(B2:B11=F1),ROW(C2:C11))-2,0,1,1) where E1 houses a class value and F1 a type value. Aladin |
|
|
|
|
May 9th, 2002, 02:22 PM
|
#8 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
You guys never fail to amaze me with what you can get done on excel, WOW.
Thanks ever so much, RET79 |
|
|
|
|
May 9th, 2002, 02:31 PM
|
#9 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Gosh this can even be extended to 3 way lookups too, amazing stuff.
RET79 |
|
|
|
|
May 9th, 2002, 02:55 PM
|
#10 |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
A related problem was the following, I had data like this
Year Policy Data1 Data2 1991 Life 2345 42420 Pension 2355 2449 1992 Life 94802 42420 Pension 4295 24249 1993 Life 2420 63729 Pension 24020 74240 etc. and I just couldn't come up with a lookup solution to find Data2 for 1992 Pension say. The required function would give me 24249. Again, I just can't change the labels and I require a lookup solution, not VBA please. This concerns thus your 2nd retrieval problem. I'll assume that A1:D7 houses your sample data which I reproduce here: {"Year","Policy","Data1","Data2"; 1991,"Life",2345,42420; "","Pension",2355,2449; 1992,"Life",94802,42420; "","Pension",4295,24249; 1993,"Life",2420,63729; "","Pension",24020,74240} where "" stands for blank. For a retrieval from "Data2" column, use; =OFFSET(D2,(MATCH(F1,A:A,0)+IF(G1="Pension",1,0))-2,0,1,1) where F1 houses a year and G1 a policy. Aladin |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
