Software

Ask something
Question Locked

Can you help me with a function?

0 helpful
4 replies

by laura.giacchino

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

=INDEX(A1:B5, MATCH($D1,A1:A5,0),$E1)

A B C D E
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!

4 replies

by aprpillai

Did you try the =VLOOKUP() Function

=VLOOKUP(C1,$A$1:$B$8,2,FALSE)
=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.

by laura.giacchino

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!!!

by ajaygautam2008

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
regards,
Ajay

This discussion is closed to new replies.