Need an alternative to Vlookup

**caliskier** · 04-24-2012, 05:27 PM

Hi,

I have created a file to explain what I am trying to do, hard to describe in words. I have a tab called "Can't Change Sheet" with several lines of numbers on it. What i want to do is link over to the "New Sheet" using a formula that I can copy down easily. A formula that looks for "Current" and the name of the object to pull in the numbers. Like i said, easy with Vlookup and concatenation, but i can't change the original sheet. Is there an easy way to do this...? I know I could use vlookup for this problem if I could run a concatenation formula on the original sheet, but i am not allowed to change anything on the original sheet, it would cause a very big mess.

Thanks in advance...

**Søren Larsen** · 04-24-2012, 05:34 PM

Try this in D3 and copy across:

=SUMPRODUCT(('Can''t Change Sheet'!$B$4:$B$39=$B3)*1,('Can''t Change Sheet'!$C$4:$C$39=$C3)*1,'Can''t Change Sheet'!D$4:D$39)

You may have to change , to ; depending on your delimiters.

**Cutter** · 04-24-2012, 05:35 PM

Maybe SUMIFS() if there is only one unique match?

=SUMIFS('Can''t Change Sheet'!D:D,'Can''t Change Sheet'!$B:$B,$B3,'Can''t Change Sheet'!$C:$C,$C3)

in D3 and copied throughout.

**ChemistB** · 04-24-2012, 05:39 PM

Another option, in D3 of new sheet
=INDEX('Can''t Change Sheet'!$D$4:$O$39,MATCH($B3&$C3,'Can''t Change Sheet'!$B$4:$B$39&'Can''t Change Sheet'!$C$4:$C$39,0),MATCH(D$2,'Can''t Change Sheet'!$D$3:$O$3,0)) entered as an array (CNTRL SHFT ENTER) and dragged across and down

**caliskier** · 04-24-2012, 09:53 PM

Thanks all for your help, I will have to think of another option, sumproduct / Sumifs will bog down my calculations as this is a huge file. I have never liked working with array formulas I just don't trust them.

Please do not take that badly, I really appreciate your help.

**Søren Larsen** · 04-25-2012, 03:00 AM

In that case, you could create an extra sheet containg the same information as "Can't change sheet", but where you add a helper column and =INDEKX(MATCH(CONCATENATE())) to retrieve the correct values...

**Cutter** · 04-25-2012, 08:27 AM

SUMIFS() is unlikely to bog down your calculations. Did you try it?

**caliskier** · 04-25-2012, 10:35 AM

I have not tried SUMIFS(), SUMIF() is a great formula but this would require 10560 sumifs, if it is not the same as sumif, maybe it would work. 10560 would bog me down. In the past I have built macros to copy and paste values the sumif, but I am linking other workbooks which would get complicated. I will try the SUMIFS() solution now and see how I fare.

**micope21** · 04-25-2012, 10:59 AM

Hi

There other way. No Array formula.
Try this
D3 =INDEX('Can''t Change Sheet'!D$4:D$40, MATCH($B3&"-"&$C3, INDEX('Can''t Change Sheet'!$B$4:$B$40&"-"&'Can''t Change Sheet'!$C$4:$C$40, 0), 0)) Then copy down and cross.