Can you help me with a function?

By laura.giacchino ·
=INDEX(A1:B5, MATCH($D1,A1:A5,0),$E1)

kiwi 93 Oranges 25
Bananas 38 Bananas 38
Oranges 25 Apples 41
Apples 41 Oranges 25
Pears 40 Pears 40
Bananas 38
kiwi 93
Pears 40

I have one reference sheet ie col A & B and another which has Col D.

I consistently need to keep entering ?fruit? in column D in another worksheet (same wb). I want to match Oranges from Column D to same value in Column A and bring value B over to populate column E.

I have been playing around with Index and Match etc etc and cannot find the right combo.
This would be a HUGE time saver for me not to have to re-enter the same data hundreds of times!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Help with Function

by aprpillai In reply to Can you help me with a fu ...

Did you try the =VLOOKUP() Function

=VLOOKUP(lookup Value,Table_Arry,Col_Index,false)

Example Function is written in Cell D1 and can be copied down to other cells. The last parameter FALSE look for exact match rather than nearest match. Please read help document in Excel.

Collapse -


by laura.giacchino In reply to Help with Function

Thank you for the quick response and great advice. I did read up on the vlookup before.. I must have misunderstood. I didn't think it would return the actual value. The solution worked perfectly!!!

Collapse -

Index & Match

by ajaygautam2008 In reply to Can you help me with a fu ...

If u have data in sheet2 like this & then sheet 3 u look the price of any type of fruit. So enter Fruit Name u will find the price of related fruit.use this formula.=INDEX(Sheet2!D2:D7,MATCH(Sheet3!C2,Sheet2!C2:C7,FALSE))

Furit Amt
kiwi 93
Bananas 38
Oranges 25
Apples 41
Pears 40
Pears 60

Related Discussions

Related Forums