Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Match 2 Columns, Return 3rd, Differing Match Types

Hello,

I have 3 columns of data in my main table. For example:

Blue / 2 / $5
Blue / 4 / $10
Blue / 6 / $15
Green / 3 / $4
Green / 10 / $8
Red / 1 / $1
Red / 4 / $3

What I am looking to do is use some kind if Index/Match function (I
think) to look up the color (column A) and number (column B) and have
the price returned (column C). I have found lots of solutions for
this out there, however the one part I keep getting stuck on is when I
need to look up something like the following...

Blue / 5 = #N/A. I want the answer to be $10 but since the number 5
is not an exact match with 2, 4 or 6 an error is returned.

How can I set up a formula where it is using a match type of 1,
instead of the exact match on the number lookup portion (column b).

Maybe I need to take a totally different route with the whole thing?
Thank you in advance for any suggestions you are able to provide!!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default Match 2 Columns, Return 3rd, Differing Match Types

Hi,

=INDEX(C1:C4,MATCH(E1,A1:A4,0)*MATCH(F1,B1:B4,1))

change range to fit your needs

"Matt.Russett" wrote:

Hello,

I have 3 columns of data in my main table. For example:

Blue / 2 / $5
Blue / 4 / $10
Blue / 6 / $15
Green / 3 / $4
Green / 10 / $8
Red / 1 / $1
Red / 4 / $3

What I am looking to do is use some kind if Index/Match function (I
think) to look up the color (column A) and number (column B) and have
the price returned (column C). I have found lots of solutions for
this out there, however the one part I keep getting stuck on is when I
need to look up something like the following...

Blue / 5 = #N/A. I want the answer to be $10 but since the number 5
is not an exact match with 2, 4 or 6 an error is returned.

How can I set up a formula where it is using a match type of 1,
instead of the exact match on the number lookup portion (column b).

Maybe I need to take a totally different route with the whole thing?
Thank you in advance for any suggestions you are able to provide!!
.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Match 2 Columns, Return 3rd, Differing Match Types

One way...

*IF* the data is grouped by color then sorted in ascending order by column
B.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default Match 2 Columns, Return 3rd, Differing Match Types

Just in case the data isn't always grouped and sorted:

=INDEX($C$2:$C$8,
MATCH(MAX(INDEX(($A$2:$A$8=E2)*($B$2:$B$8<=F2)*$B$ 2:$B$8,)),
INDEX(($A$2:$A$8=E2)*$B$2:$B$8,),0))

HTH
Steve D.


"T. Valko" wrote in message
...
One way...

*IF* the data is grouped by color then sorted in ascending order by column
B.

Assuming the data is in the range A2:C8

E2 = lookup color
F2 = lookup number

=LOOKUP(F2,OFFSET(B2:C2,MATCH(E2,A2:A8,0)-1,,COUNTIF(A2:A8,E2)))

--
Biff
Microsoft Excel MVP


"Matt.Russett" wrote in message
...
Hello,

I have 3 columns of data in my main table. For example:

Blue / 2 / $5
Blue / 4 / $10
Blue / 6 / $15
Green / 3 / $4
Green / 10 / $8
Red / 1 / $1
Red / 4 / $3

What I am looking to do is use some kind if Index/Match function (I
think) to look up the color (column A) and number (column B) and have
the price returned (column C). I have found lots of solutions for
this out there, however the one part I keep getting stuck on is when I
need to look up something like the following...

Blue / 5 = #N/A. I want the answer to be $10 but since the number 5
is not an exact match with 2, 4 or 6 an error is returned.

How can I set up a formula where it is using a match type of 1,
instead of the exact match on the number lookup portion (column b).

Maybe I need to take a totally different route with the whole thing?
Thank you in advance for any suggestions you are able to provide!!




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
Lookup Formula: Return 1st match, then 2nd match, then 3rd match Scott Excel Discussion (Misc queries) 4 December 11th 09 06:50 AM
match 2 columns and return value of another Haz Excel Worksheet Functions 1 July 8th 08 01:46 PM
need to return next match of table, only finding first match...HEL nwtf_vol[_2_] Excel Worksheet Functions 0 January 23rd 08 02:42 AM
MATCH Multiple Criteria & Return Previous / Penultimate Match Sam via OfficeKB.com Excel Worksheet Functions 27 October 6th 07 01:39 AM
Match two columns, return a third piece of data uncreative Excel Discussion (Misc queries) 2 April 25th 07 12:40 AM


All times are GMT +1. The time now is 07:05 PM.

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"