Removing a helper column to join two fields for a lookup

[benoj2005]

joining two fields for lookups without helper column.xlsx

Anyone able to remove column A and still maintain the result in cell H3 ?

[mike7952]

@benoj2005,

Delete column A and use this forluma in G3 =SUMPRODUCT(--(E3=A3:A5)*(F3=B3:B5)*(C3:C5))

[dilipandey]

Hi benoj2005,

Yes.. try using below array formula :-

{=INDEX($B$3:$D$5,MATCH($F3&$G3,$B$3:$B$5&$C$3:$C$5,0),3)}

See attached:-
joining two fields for lookups without helper column.xlsxjoining two fields for lookups without helper column.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[K m]

Have you thought about hiding column A?

[benoj2005]

@benoj2005,

Delete column A and use this forluma in G3 =SUMPRODUCT(--(E3=A3:A5)*(F3=B3:B5)*(C3:C5))

Thanks but my actual data was text that needed to be returned so a sumproduct obviously produced a #value

Hi benoj2005,

Yes.. try using below array formula :-

{=INDEX($B$3:$D$5,MATCH($F3&$G3,$B$3:$B$5&$C$3:$C$5,0),3)}

See attached:-
Attachment 171901Attachment 171901

Regards,
DILIPandey

<click on below 'star' if this helps>

Cheers, works a treat, I'd prefer a non array if possible as it's potentially going to be 100's of thousands of rows of data and arrays kill my machine then

Have you thought about hiding column A?

No, becomes messy for others to maintain.

[Fotis1991]

Maybe?

=INDEX(D3:D5,MATCH(F3,B3:B5,0),MATCH(G3,C3:C5,0))

[benoj2005]

Maybe?

=INDEX(D3:D5,MATCH(F3,B3:B5,0),MATCH(G3,C3:C5,0))

That dosen't work, you're trying to put a match on a row into the index column argument..

[Fotis1991]

If you say so...