I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

**captnken** · 12-06-2013, 09:32 AM

In using the following formula:

=ADDRESS(MATCH($A26&13,[transactions.xlsx]payments!$A2:$A2000&[transactions.xlsx]payments!$B2:$B2000),3,4,1,"[transactions.xlsx]payments")

I get #value returned. When doing the evaluate it shows the value from the MATCH as returning the #value to the field. I cannot figure what is causing this as when I evaluate the first two fields in the MATCH formula they appear to look ok... Ideas?

**JBeaucaire** · 12-08-2013, 11:17 AM

The use of the & operator in $A2&13 creates a TEXT string, even if $A2 is numeric as is 13. You would need to convert that string BACK into a number, perhaps by: ($A2&13)+0

Give that a try.

**captnken** · 12-08-2013, 07:11 PM

Originally Posted by JBeaucaire

The use of the & operator in $A2&13 creates a TEXT string, even if $A2 is numeric as is 13. You would need to convert that string BACK into a number, perhaps by: ($A2&13)+0

Give that a try.

Thanks for the reply. I tried this but can't seem to figure out how. The formula I am using is:
ADDRESS(MATCH($A26&13,[transactions.xlsx]payments!$A2:$A2000&[transactions.xlsx]payments!$B2:$B2000),3,4,1,"[transactions.xlsx]payments")

A26 of the current worksheet is actually from the current row and reflects the row referenced, i.e. each row contains the same formula. payments are the workbook/worksheet where the payments are located. Column A contains units (apartment numbers, i.e. 23A, 23B, etc) and oolumn B contains year, 9,10,11... Column 3 (or C) of payments contains the date i am attempting to locate.

I'm trying to locate the last payment made for a year (i.e. 13) as the value of this field.

Thank you for your interest...

**captnken** · 12-08-2013, 07:16 PM

As additional information:
When I look at the Functions Arguments window the results are almost correct. the sheet_text value as the last argument in the ADDRESS function shows "[transactions.xlsx]payments!C574". I do know if a problem exists with the $ missing or how to get it in the argument.

**XOR LX** · 12-08-2013, 07:23 PM

Hi,

I'm not sure about this part of your formula:

MATCH($A26&13,[transactions.xlsx]payments!$A2:$A2000&[transactions.xlsx]payments!$B2:$B2000)

Firstly, unless this is entered as an array formula, it will not do as you wish. Secondly, are you trying to find an exact match? If so, you have omitted the match_type parameter.

Perhaps this is what you meant (I have used an INDEX to make it non-array):

=ADDRESS(MATCH($A26&13,INDEX([transactions.xlsx]payments!$A2:$A2000&[transactions.xlsx]payments!$B2:$B2000,,),0),3,4,1,"[transactions.xlsx]payments")

though I can't be sure without seeing an actual sheet.

Regards

**captnken** · 12-08-2013, 07:42 PM

Thanks again... the actual results display in the cell as [transactions.xlsx]payments!$C$571 not the value of same. and the 571 is 4 cells less than the actual one i'm looking for (575). any change i could share the file with you so you might see what i'm trying to do?

**XOR LX** · 12-08-2013, 07:46 PM

If it's the actual cell value that you're wishing to return, why then are you using the ADDRESS function? Sounds like INDEX is what you require.

I think this would be a good time for you to post a workbook.

Regards

**captnken** · 12-08-2013, 07:55 PM

Another perspective: I am trying to locate the latest payment date within an account/year.

**XOR LX** · 12-08-2013, 07:57 PM

That's all very well, but without seeing an actual spreadsheet it's not going to be possible to be sure of a correct solution.

Regards

**captnken** · 12-08-2013, 08:02 PM

ok, here is a sample of data. using a reference of 21A13 I want to select the highest date within year 13 for act 21A, i.e. 11/06/13.

21A 12 03/01/12
21A 12 03/30/12
21A 12 05/01/12
21A 12 05/10/12
21A 13 05/01/12
21A 13 05/01/13
21A 13 05/01/13
21A 13 11/06/13
21A 14 11/27/13
21B 07 05/13/08
21B 07 06/24/08

**XOR LX** · 12-08-2013, 08:10 PM

I meant an actual spreadsheet, but anyway, assuming that data is in A1:C11:

=MAX(INDEX((A1:A11="21A")*(B1:B11=13)*C1:C11,,))

Regards

**captnken** · 12-08-2013, 08:12 PM

thanks, i'll try it...

**martindwilson** · 12-08-2013, 08:19 PM

array entered
=MAX(IF((A1:A11="21a")*(B1:B11=13),C1:C11))

**XOR LX** · 12-08-2013, 08:22 PM

@martindwilson

I must be dreaming! You suggesting an array formula when there's a perfectly good non-array version??

Unheard of!!

**martindwilson** · 12-08-2013, 08:26 PM

i know ,i did it then thought hmmmm ..then to compound it i saw your post lol

**captnken** · 12-08-2013, 09:11 PM

EXCELLENT! Thank you all....

**captnken** · 12-08-2013, 09:35 PM

Well, ya'll are way beyond me... But I sure do appreciate your help!

**JBeaucaire** · 12-08-2013, 10:31 PM

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

LinkBack

Thread Tools

Rate This Thread

Display

I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Re: I'm trying to MATCH on 2 columns using & and place value using ADDRESS in field.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] match 2 columns in the 1st place and then 2 other columns in the 2nd place

Partial IP Address Range Match Between 2 Columns

[SOLVED] Cross reference two columns (different workbooks) and then populate a field if match

[SOLVED] How to split an address field into two columns

How to Reference a Web Query's Address Field to an external field?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions