Lookup Formula

[BradleyS]

Hi Marcelo Branco gave me some help with a formula as shhown below:

Array-Formula in G3
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($D$2:$D$13,MATCH(1,IF($A$2:$A$13=$F3,IF(ISNUMBER(SEARCH(G$2,$C$2:$C$13)),1))))))

Which works very well in this table:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ID	DATE	CODE	DESC			BLUE	ORANGE	RED
2	1	13/11/2001	C100112	TEXT2		ID	C10	2126300	9h4
3	85	09/01/2009	C10..00	TEXT1		1	TEXT2
4	227	21/07/1993	C100112	TEXT2		85	TEXT1
5	276	05/02/2007	C10..00	TEXT1		227	TEXT2
6	278	21/02/2008	C10..00	TEXT1		276	TEXT1
7	278	21/02/2008	2126300	TEXT3		278	TEXT1	TEXT3
8	283	18/08/2008	2126300	TEXT3		283		TEXT3
9	288	26/02/1998	C100112	TEXT2		288	TEXT2
10	2049	18/05/2011	9h43.00	TEXT5		2049	TEXT6		TEXT5
11	2049	21/06/2011	C10E600	TEXT6		1906	TEXT7		TEXT4
12	1906	10/10/2010	9h4..00	TEXT4
13	1906	11/08/2009	C109.12	TEXT7
Sheet2

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

What I would like to do now is show the Dates in column B under each of the Blue, Orange and Red columns, instead of showing the DESC.

Please could someone help me change the code to make this work?

I thought it would be easy but I was very wrong!

 

[BradleyS]

I think I should also explain:

It needs to match the id's in both tables
Then if the code is the same as what is under BLUE, ORANGE or RED it should display the date this time around.
Instead of the text desc which it is currently set up to do.

 

[BradleyS]

I can now see that I need to change the INDEX to column B to show the date instead of the text in column D.

Array formula:
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($B$2:$B$13,MATCH(1,IF($A$2:$A$13=$F3,IF(ISNUMBER(SEARCH(G$2,$C$2:$C$13)),1))))))

However, the date will not show and I don't know why, could someone tell me what I'm missing?

 

[BradleyS]

OK I think it's the REPT function causing the problem as it only uses text values.

When I'm now looking at a date value, therefore what can I use it in place?

 

[Aladin Akyurek]

I can now see that I need to change the INDEX to column B to show the date instead of the text in column D.

Array formula:
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($B$2:$B$13,MATCH(1,IF($A$2:$A$13=$F3,IF(ISNUMBER(SEARCH(G$2,$C$2:$C$13)),1))))))

However, the date will not show and I don't know why, could someone tell me what I'm missing?

When a numeric value is expected..., modify it to:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX($B$2:$B$13,MATCH(1,IF($A$2:$A$13=$F3,IF(ISNUMBER(SEARCH(G$2,$C$2:$C$13)),1))))))

which must be confirmed with control+shift+enter, not just enter.

Moreover, you'll need to custom format the formula cell as e.g.:

[=0]"";d/m/yyyy

 

[BradleyS]

That's perfect thank you.

However, can I not just format the cells using Date, what does [=0]"";d/m/yyyy do?

 

[Aladin Akyurek]

That's perfect thank you.

You are welcome.

However, can I not just format the cells using Date, what does [=0]"";d/m/yyyy do?[/QUOTE]

The formula returns a 0 ("" is imppossible) when no match is found. A regular date formatting would display cells with 0 outcome as: 1/0/1900.

[=0]"";d/m/yyyy allows to display them as if they are blank.

 

[BradleyS]

Ah I see.

However, when no match is found I get an #N/A not a zero value

 

[BradleyS]

My fault I just found another change in your code from "" to 0, which now makes perfect sence.

Thank you very much

 

[Aladin Akyurek]

My fault I just found another change in your code from "" to 0, which now makes perfect sence.

Thank you very much

You are welcome. Thanks for providing feedback.