Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mal
 
Posts: n/a
Default Matching Values from an Array

I hope someone can help me on this one.

I have a column of numbers in A1-A6.
I have three columns (B:D) of combinations of the words "Greater", "Equal",
"Less"
I have a table (A10:D18) four columns wide and any rows long which include
"Greater", "Equal", "Less" plus a value
I want to match the words in B1:D1 with the table A:C and multiply the
corresponding values in column A and column D.
So match b1:d1 with Table A11:C11, multiply 5*1.9 and produce the result 9.5
in E.

A B C D E
1 5 greater greater equal (Result 9.5 (5*1.9))
2 3 equal greater greater (Result 4.8 (3*1.6))
3 6 less greater less (Result 6.6 (6*1.1))
4 9 less greater equal (Result 10.8 (9*1.2))
5 7 equal greater less (Result 9.8 (7*1.4))
6 2 greater greater less (Result 3.4 (2*1.7))
There can be any combinations of these words in random order.

Table A10:D18
A B C D
10 Greater Greater Greater 2
11 Greater Greater Equal 1.9
12 Greater Greater Less 1.7
13 Equal Greater Greater 1.6
14 Equal Greater Equal 1.5
15 Equal Greater Less 1.4
16 Less Greater Greater 1.3
17 Less Greater Equal 1.2
18 Less Greater Less 1.1
There are other combinations but you can see the columns are in order.

I hope this all makes sense.
Thanks,
Mal




  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Matching Values from an Array

=A1*INDEX($D$10:$D$18,MATCH(B1&C1&D1,$A$10:$A$18&$ B$10:$B$18&$C$10:$C$18,0))

this is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mal" wrote in message
...
I hope someone can help me on this one.

I have a column of numbers in A1-A6.
I have three columns (B:D) of combinations of the words "Greater",

"Equal",
"Less"
I have a table (A10:D18) four columns wide and any rows long which include
"Greater", "Equal", "Less" plus a value
I want to match the words in B1:D1 with the table A:C and multiply the
corresponding values in column A and column D.
So match b1:d1 with Table A11:C11, multiply 5*1.9 and produce the result

9.5
in E.

A B C D E
1 5 greater greater equal (Result 9.5 (5*1.9))
2 3 equal greater greater (Result 4.8 (3*1.6))
3 6 less greater less (Result 6.6 (6*1.1))
4 9 less greater equal (Result 10.8 (9*1.2))
5 7 equal greater less (Result 9.8 (7*1.4))
6 2 greater greater less (Result 3.4 (2*1.7))
There can be any combinations of these words in random order.

Table A10:D18
A B C D
10 Greater Greater Greater 2
11 Greater Greater Equal 1.9
12 Greater Greater Less 1.7
13 Equal Greater Greater 1.6
14 Equal Greater Equal 1.5
15 Equal Greater Less 1.4
16 Less Greater Greater 1.3
17 Less Greater Equal 1.2
18 Less Greater Less 1.1
There are other combinations but you can see the columns are in order.

I hope this all makes sense.
Thanks,
Mal






  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mal
 
Posts: n/a
Default Matching Values from an Array

Thanks Bob,
Everything works perfectly.
Mal

"Bob Phillips" wrote in message
...
=A1*INDEX($D$10:$D$18,MATCH(B1&C1&D1,$A$10:$A$18&$ B$10:$B$18&$C$10:$C$18,0))

this is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mal" wrote in message
...
I hope someone can help me on this one.

I have a column of numbers in A1-A6.
I have three columns (B:D) of combinations of the words "Greater",

"Equal",
"Less"
I have a table (A10:D18) four columns wide and any rows long which
include
"Greater", "Equal", "Less" plus a value
I want to match the words in B1:D1 with the table A:C and multiply the
corresponding values in column A and column D.
So match b1:d1 with Table A11:C11, multiply 5*1.9 and produce the result

9.5
in E.

A B C D E
1 5 greater greater equal (Result 9.5 (5*1.9))
2 3 equal greater greater (Result 4.8 (3*1.6))
3 6 less greater less (Result 6.6 (6*1.1))
4 9 less greater equal (Result 10.8 (9*1.2))
5 7 equal greater less (Result 9.8 (7*1.4))
6 2 greater greater less (Result 3.4 (2*1.7))
There can be any combinations of these words in random order.

Table A10:D18
A B C D
10 Greater Greater Greater 2
11 Greater Greater Equal 1.9
12 Greater Greater Less 1.7
13 Equal Greater Greater 1.6
14 Equal Greater Equal 1.5
15 Equal Greater Less 1.4
16 Less Greater Greater 1.3
17 Less Greater Equal 1.2
18 Less Greater Less 1.1
There are other combinations but you can see the columns are in order.

I hope this all makes sense.
Thanks,
Mal








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 03:03 AM
counting unique values and matching to quantities [email protected] Excel Worksheet Functions 2 September 9th 05 11:25 PM
find maximum of two values in an array with same lookup value Andy M Excel Discussion (Misc queries) 5 May 13th 05 01:31 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM


All times are GMT +1. The time now is 07:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"