Vlookup and multiple criteria

[Chaffers]

I've a sheet of data with a product line and a rank and I want to do a vlookup which picks up the product line but also the rank so I can derive a top ten list for a particular product. At the moment as I am only looking up the product line, I only ever retrieve the first record. Does anyone have any ideas, ideally without vba?! May thanks, Chaffers

 

[onlyadrafter]

Hello,

Do you mean

=VLOOKUP(A1&A2,table,column,0)

if not use the search facility and enter 'vlookup and multiple' there will be a lot of results, maybe you can find one to suit.

 

[Chaffers]

Thanks for this, possibly what I want though get an #n/a .. think I am more after being able to do vlookup(a1&b1,table,column,0) as rank is in a separate col. data looks like:

Product Rank
a 1
a 2
b 1

Sure this doesn't make much sense .. !!

Thanks,
Chaffers

 

[patrickmuldoon99]

I think I have had a similar problem to you.... and i believe you're halfway there.

Essentially what you want to do is have an index, that you can conduct a VLookup on, that allows both product and rank to be searched together.

If you have Product in column A, and rank in column B, in column C concatenate the two into one cell. For example

In Column C, Row 1, you would write

=A1 & B1

You then do the VLookup as per earlier in this thread, but lookup on that column (which is unique as a combination of product and rank)

 

[Chaffers]

Have gone the concatanation route and does the trick.

Thanks eveyone,
Chaffers