Look-up

[mun_kut]

Hi

I have two sheets, - and the data in Sheet1 is like this:

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" width="64" height="17">A</td> <td class="xl22" style="width: 48pt;" width="64">B</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Apple</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Banana</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Orange</td> <td>
</td> </tr> </tbody></table>
Data in Sheet2 is like this:
<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">A</td> <td style="width: 48pt;" width="64">B</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Orange</td> <td align="right">32</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr> </tbody></table>I want to write a formula in the B cell of Sheet 1 so that the value "32" is returned for Orange in Sheet 2. Any good way of doing this?

Thanks!

 

[Jonmo1]

try this in Sheet 1 B2

=INDEX('Sheet 2'!B:B,MATCH(A2,'Sheet 2'!A:A,0))

 

[mun_kut]

Excellent!

I am actually having multiple matches. Sheet B actually looks like this:

A B C
Orange 32 Juice
Orange 21 Fruit
Apple 45 Fruit
Apple 32 Juice

And I want the value returned only when the C column value is "Juice". Can I use an IF clause in this formula?

Thanks.