formula to find a value

[Guest]

I have a spreadsheet with 2 worksheets, on the first worksheet I have 3
columns, column A has a number between 1 and 50, column b has a number
between 1 and 10 and column c has text string that varies. On the second
worksheet I have set up a grid with 1-50 in column A, and 1-10 in row 1, I
need a formula look on the first worksheet and find the value in column C
that correspond to the grid values on the second worksheet , this is a very
simplified example and the columns are actually columns G H and I.

I hope this makes sense…

Worksheet 1

A B C
1 1 snow
1 2
1 3 house
2 1 yes
2 2
3 1
4 1 no

Worksheet 2
A B C D

1 2 3
1 snow house
2 yes
3
4 no

 

[Bob Phillips]

Assuming they are called Sheet2 and Sheet3

=IF(ISNUMBER(MATCH(1,(Sheet3!$A2=Sheet2!$A$1:$A$10)*(Sheet3!B$1=Sheet2!$B$1:$B$10),0)),INDEX(Sheet2!$C$1:$C$100,MATCH(1,(Sheet3!$A2=Sheet2!$A$1:$A$10)*(Sheet3!B$1=Sheet2!$B$1:$B$10),0)),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

Expanding on Bob's suggestion...

Where column C has empty cells Bob's formula will return 0s. You can trap
both those 0s and any errors by modifying the formula to: (provided that all
data in column C is TEXT as the sample demonstrates)

=IF(ISTEXT(INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet3!$A2=Sheet2!$A$1:$A$10)*(Sheet3!B$1=Sheet2!$B$1:$B$10),0))),INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet3!$A2=Sheet2!$A$1:$A$10)*(Sheet3!B$1=Sheet2!$B$1:$B$10),0)),"")

However, that's a real "mouthful" not to mention it's not very efficient
since it has to process the data twice.

It may be better to use a simpler formula and then use conditional
formatting to hide the 0s and any errors.

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet3!$A2=Sheet2!$A$1:$A$10)*(Sheet3!B$1=Sheet2!$B$1:$B$10),0))

Then use CF to hide 0s and any errors:

Select the range of formulas
Goto Format>Conditional Formatting
Formula Is: =NOT(ISTEXT(B2))
Set the font color to be the same as the fill color.

OR...

You can concatenate an empty TEXT string to column C and those cells that
would otherwise return a 0 will now return the empty text string (blank ""):

=IF(ISNUMBER(MATCH(1,(Sheet3!$A2=Sheet2!$A$1:$A$10)*(Sheet3!B$1=Sheet2!$B$1:$B$10),0)),INDEX(Sheet2!$C$1:$C$10&"",MATCH(1,(Sheet3!$A2=Sheet2!$A$1:$A$10)*(Sheet3!B$1=Sheet2!$B$1:$B$10),0)),"")