Return a value based on 2 variables in one column.

[mysticmoron109]

Hello all,

What I need:

A formula in column G that
1) looks at column M (column M has two variables "BX" and "GL")
2) if column M says "BX" then return a lookup on column A (the lookup value appears twice in column A, once for "BX" and once for "GL") and return the date listed in column F that corresponds with the lookup value for "GL"
3) And the other way around... if column G says "GL" then lookup the value in column A and return the corresponding date in column F for "BX"

In summation:

Column A has the lookup value. The lookup value can appear more than once in column A.
Column G is the empty column that needs a value returned to it.
Column M has two variables that drive the formula... "BX" and "GL"
Column F has the date that needs to be returned to column G.

Some background:

I have developed a pivot table reconciliation. It combines two records of data (identified by column M GL/BX). The unique identifier, the lookup value, can appear (should appear) more than once in column A. I need a formula that will return the date in which the record appears in the BX dataset to correspond to the GL data.

FOr example, if "ABC" was booked in the BX on Jun15, and "ABC" was booked in the GL on Jul15 then:
A...............F.............G........................................................M
ABC...........Jun15........Jul15 (returned by formula).....................BX
ABC...........Jul15.........Jun15(returned by formula)......................GL


thanking you in advance, please let me know if you need me to clarify anything furhter.

Best
M

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[bebo021999]

Try in G1:

=IFERROR(LOOKUP(2,1/(($A:$A=A1)*($M:$M<>M1)),$F:$F),"")

[mysticmoron109]

Try in G1:

=IFERROR(LOOKUP(2,1/(($A:$A=A1)*($M:$M<>M1)),$F:$F),"")

edited to troubleshoot. will post results. any other ideas?

[mysticmoron109]

=IFERROR(LOOKUP(2,1/(($A:$A=A6)*($P:$P<>$P6)),$D:$D),"")

that is the formula that i ended up using and it works very well.

However, it is extremely slow and almost crashes the workbook. any idea why? or is there any other formula that can make this more efficient? perhaps make this an index/match formula?

[bebo021999]

Try to avoid using entire range reference like A:A, limit to actual size, i.e A1:A100.