Referencing another worksheet

  • Thread starter Thread starter Carena
  • Start date Start date
C

Carena

What I am trying to do is find a row in sheet 1 that matches my two reference
cells and pulling the information from a specific colume for that row. All I
have been able to find is formulas that use one reference point.
 
What you can do is to introduce a new column in Sheet1 (eg column C) and
concatenate your two reference fields together, eg:

=A1&B1

and copy this down.

Then you can use VLOOKUP like this in Sheet2:

=VLOOKUP(A1&B1,Sheet1!C:F,4,0)

where the values you are looking for are in A1 and B1.

Hope this helps.

Pete
 
If the information that you're "pulling" is a number, and there are no
duplicate matches, you can use Sumproduct().

With datalist in A1 to C100, where criteria 1 can be found in Column A,
and criteria 2 can be found in Column B, and you want the numeric value from
Column C returned,
enter the Column A criteria in say D1, and the Column B criteria in D2, and
try this:

=Sumproduct((A2:A100=D1)*(B2:B100=D2)*C2:C100)

On the other hand, if the data to be returned is *not* numeric,
using the same scenario, try this *array* formula:

=Index(C1:C100,Match(1,(A1:A100=D1)*(B1:B100=D2),0))
 
Back
Top