Complex VLOOKUP with LEN formula

[NJS1982]

Hi all,
I have a formula that goes like this:

=IF(A12604="","",
IF(LEN(VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("ORGANISATION_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))
+LEN(VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("PROJECT_CONTACT_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))=0,W12604,
IF(LEN(VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("ORGANISATION_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))>1,
VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("ORGANISATION_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0),
VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("PROJECT_CONTACT_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))))

But it doesn't seem to work

I am trying to say this:

1) If cell A12604 is blank, make this cell blank: =IF(A12604="","",

2) If cell A12604 isn't blank, lookup the LEN values of the two cells matching A12604 on the 'Org Postcodes' data range, if together they equal zero, provide the result of W12604: IF(LEN(VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("ORGANISATION_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))
+LEN(VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("PROJECT_CONTACT_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))=0,W12604,

3) If cell A12604 isn't blank, lookup the LEN values of the Org Postcode cell matching A12604 on the 'Org Postcodes' data range, if this is greater than 1 (as in it isn't blank and has an entry), provide me with the value of this 'Organisation Postcode' lookup value:
IF(LEN(VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("ORGANISATION_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))>1,
VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("ORGANISATION_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0),

4) If the value is however zero, then provide the value of the Project_Contact_Postcode instead (as if this was zero, then point 2 above would work first and provide the W cell value instead of this one):
VLOOKUP(A12604,'Org Postcodes'!$A$1:$P$105190,MATCH("PROJECT_CONTACT_POSTCODE",'Org Postcodes'!$A$1:$P$1,0),0))))

 

[AlanY]

the match() function will only give you the position within the array, not the content.

you need to combine it with the index() function for the content in order to match them

 

[NJS1982]

Oh crikey, is it that complicated?
If I give a very simple example below...I can't believe I can't nest a formula to look at three cells independently

Which one to use?	Org	PC	Loc1
Org	L22 3NR	L6 0GQ	L3 8DL
Org	L11 3NR	L6 0GQ	L3 8DL
PC		L6 0GQ	L3 8DL
Org	L11 3NR	L6 0GQ	L3 8DL
Loc1			L3 8DL
Org	L11 3NR	L6 0GQ	L3 8DL

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[AlanY]

something like this?

Excel 2013/2016
	A	B	C	D	E	F
1		Org	PC	Loc1		Which one to use?
2	Org	L22 3NR	L6 0GQ	L3 8DL		L22 3NR
3	Org	L11 3NR	L6 0GQ	L3 8DL		L11 3NR
4	PC		L6 0GQ	L3 8DL		L6 0GQ
5	Org	L11 3NR	L6 0GQ	L3 8DL		L11 3NR
6	Loc1			L3 8DL		L3 8DL
7	Org	L11 3NR	L6 0GQ	L3 8DL		L11 3NR
Sheet5

Cell Formulas
Range	Formula
F2	=INDEX(B2:D2,MATCH($A2,$B$1:$D$1,0))

 

[NJS1982]

No, not really. Sorry I have not explained - column A in this example does not exist. I simply added it to demonstrate which one should be shown, say in column F.
The lookup is against a unique number and the table array is massive (>100k lines).

 

[AlanY]

something like this?

Excel 2013/2016
	A	B	C	D	E	F
1		Org	PC	Loc1		Which one to use?
2	Org	L22 3NR	L6 0GQ	L3 8DL		L22 3NR
3	Org	L11 3NR	L6 0GQ	L3 8DL		L11 3NR
4	PC		L6 0GQ	L3 8DL		L6 0GQ
5	Org	L11 3NR	L6 0GQ	L3 8DL		L11 3NR
6	Loc1			L3 8DL		L3 8DL
7	Org	L11 3NR	L6 0GQ	L3 8DL		L11 3NR
Sheet5

Cell Formulas
Range	Formula
F2	=INDEX(B2:D2,MATCH($A2,$B$1:$D$1,0))

ok, would you explain how do you come up with Org in A2 and Loc1 in A6 as examples