Conditional VLookup?

Hi there,

I have a quick question, probably something really simple.

I've got a spreadsheet with lots of data in it and on another Tab I'm trying to do a summary of the data in various ways.

I am doing a simple VLookup for the basic tables, but is there a way of doing a VLookup based on 2 criteria?

I.e, I can do a lookup if, lets say, column G contains the word "Material". i can then set the column of the data I want it to return.

But, how can I set 2 criteria? I.e Column G contains the word "Material" AND Column H contains the word "Operations", for it to then return only the values in Column V that meet this criteria?

Do I have to nest the Vlookup in an IF statement? Or is there a much easier way?

Thanks in advance for any help!!!

Much appreciated.

Arun

Originally Posted by arun.sharma

But, how can I set 2 criteria? I.e Column G contains the word "Material" AND Column H contains the word "Operations", for it to then return only the values in Column V that meet this criteria?

What sort of values are there in column V, numbers or text?

What do you want to do if there are several matches, list them all (text), sum them (numbers), or something else?

I'm not sure how that would work tho and I havent used that particular formulae before. Any advice on it!?

I've attached an example of what I'm trying to achieve.

Thanks for such a swift response guys, especially on a Friday!!

You could use this formula in Summary!B4 copied across and down to D7

=SUMPRODUCT(--('Raw Data'!$G$3:$G$20=$A4),--('Raw Data'!$B$3:$B$20=B$3),'Raw Data'!$H$3:$H$20)

BUT this will only work if you have EXACT matches, so "Operations" in C3 doesn't match the "OPS" you use in the other worksheet and DI3 etc. doesn't match the numbers (without "DI") that you have in the raw data worksheet, so you'd need to change these

[you could put just 2 in A4 but format as "DI"0 ]

Worked like a treat!

Thanks for your help guys, have a nice weekend!