Spreadsheet: Get cell data if 2 conditions are met. Help?

1

Hi, I've found a method to create the results you want. It is not fanciful but it works.

You can see my spreadsheet here: http://apps.memopal.com/d/?SUUUV2T5

There are a few assumptions I made.

1) The names are spelled in the same manner

e.g. Cathy in Sheet1 Name1 is spelled as Cathy in Sheet2 Name1, and not cathy or CATHY etc

2) All [Name1 n Name2] columns are sorted in ascending order (more info below)

3) The sorting of [Name1 n Name2] columns must be done before the Lookup formula is written into Sheet1.

Since each Name1+Name2 would be a unique index, I created a new column in Sheet1 and Sheet2 where I use Concatenate function to put them together. I label this column [Name1 n Name2].

After that's done, I sorted the table of each worksheet according to [Name1 n Name2] in ascending order. This is important otherwise the lookup formula to find the number in Sheet2 Col B will not work properly.

In Sheet1, I created another column to get the number in Sheet2 Col B. The formula I use is :

=LOOKUP(C2,Sheet2!D2:D8,Sheet2!B2:B8)

This formula will do the following

i) See what's in the [Name1 n Name2] cell I indicated in Sheet1

ii) Go to the indicated range of rows in [Name1 n Name2] of Sheet2 and find a match.

iii) Once a match is found, get the data from the same row in Column B

iv) Return the data to Sheet1

Minor problem: Once you copy/drag the lookup formula to the cells below it, it will become

=LOOKUP(C3,Sheet2!D3:D9,Sheet2!B3:B9)

=LOOKUP(C4,Sheet2!D4:D10,Sheet2!B4:B10)

=LOOKUP(C5,Sheet2!D5:D11,Sheet2!B5:B11)

etc

As you can see, the range of rows to look thru moves down by one row each time. Excel does this automatically. This is why the [Name1 n Name2] columns must be sorted in ascending order before the lookup formula is written into Sheet1. That way as the range of rows to search moves downward, you won't miss a thing.

Alternative: Change =LOOKUP(C2,Sheet2!D2:D8,Sheet2!B2:B8) to

=LOOKUP(C2,Sheet2!$D$2:$D$8,Sheet2!$B$2:$B$8). After that when you copy and paste the formula downward, the cell reference of Sheet2 will remain the same.

Another alternative: write this formula

=LOOKUP(C2,Sheet2!Dx:Dy,Sheet2!Bx:By)

Copy and Paste the formula into each cell of the Sheet 1 column where u want the Sheet 2 Col B data to be.

Select that column then use 'Find and Replace' to replace x with the first Row number that has data in Sheet2 and replace y with the last Row number that has data in Sheet2.

The disadvantage of this is, you would need to remember this alternative and repeat it if u add more rows to Sheet 1 in the future.

I hope this will help you.