MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup - more conditions


Posted by Andonny on February 28, 2001 2:21 PM

Hi,
I have been trying to work out a vlookup formula but I couldn't make it to work.
I have on sheet2:
.........A..........B...........C(Price)
1.....11........pear........0.59
2.....21........apple.......0.75
I am looking for a formula in C column to =VLOOKUP(A2,Sheet1!A3:D8,4,"FALSE") but only to display the price where it matches 1 from culumn C
In this particular case it would be D3(for pear - 0.59) and D6(for apple - 0.75)

sheet1
.........A..........B...........C........D(price)
1.....11........pear........2........0.55
2.....11........pear........8........0.57
3.....11........pear........1........0.59
4.....21........apple.......2........0.82
5.....21........apple.......8........0.81
6.....21........apple.......1........0.75

Thanks a million for your help
Andonny


Posted by Aladin Akyurek on February 28, 2001 2:59 PM

Insert a column for the column in which your prices are on Sheet1.
Assuming that the values on Sheet1 now occupy a range from A1 on including the extra column. Name the subrange from D1 on (d.i. D1:E6) DATA.

In D1 enter: =B1&"-"&C1

Enter in some cell the criteria 1 and call the cell e.g., INDIC (abbreviation of INDICATOR) on Sheet2.

On Sheet2 enter

C1 =VLOOKUP(B1&"-"&INDIC,DATA,2,0) [ copy down as far as needed ]

Aladin


Posted by Andonny on March 01, 2001 2:40 AM

Hi Aladin,
Thanks for your reply.
I copied down the =B1&"-"&C1 but I didn't quite understand what you mean with subrange and as well what I need to do on sheet2.

It looks interesting and hope for an example.

Thanks a million
Andonny

Posted by Aladin Akyurek on March 01, 2001 3:02 AM

The workbook is underway to you.

Aladin