lookup across two columns then get the corresponding data.

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
On 2002-05-09 12:16, RET79 wrote:
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

Insert a new column before Class which combines the Class & Type columns by doing =B1&C1 in column D. Now use the new column in your vlookup formula:

=Vlookup("ASS507",table,4,false)
 
Upvote 0
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
 
Upvote 0
On 2002-05-09 12:24, Mark W. wrote:
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))}

---snip---

Nice one! I use this multi-way lookup frequently, but never like this. Cool.
 
Upvote 0
On 2002-05-09 12:16, RET79 wrote:
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
Ignore the 1st part I misread the question and others have posted good alternatives.

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

I think this is the easiest way to get a table that you can use.

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.


_________________<A HREF= "http://website.lineone.net/~s-o-s/Index.html">
image001.gif

This message was edited by s-o-s on 2002-05-09 13:34
 
Upvote 0
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
 
Upvote 0
You guys never fail to amaze me with what you can get done on excel, WOW.

Thanks ever so much,

RET79
 
Upvote 0
Gosh this can even be extended to 3 way lookups too, amazing stuff.

RET79
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top