"Double" Vlookup

[Matt Colon]

Good afternoon everyone,

I'm looking for a type of function that lets you do a VLOOKUP, but with TWO
criteria. I have a spreadsheet with 3 columns - the combination of data in
column A and B for any given row is unique. What I need is to be able to
specify a value to look for in column A, a value to look for in column B,
and then return the value in column C for that one corresponding row where
the criteria was met for both column A & B simultaneously. I have a fairly
crude VBA macro that does this, but I was looking for any advice to see if
this can be done without VBA. Sorting the data is very clumsy because there
are often over 30,000 rows of data. Any help is greatly appreciated.
Thanks.

Regard,

Matt Colon

 

[Frank Kabel]

Hi
one way:
- create a new helper column A
- enter the following in A1: = B1 & C1
- copy this for all rows

No you can use VLOOKUP on this first column. e.g. your criteria are in
cells E1 and F1 try
=VLOOKUP(E1&F1,A11000,4,0)

 

[Mark Graesser]

Matt
You insert a column in your lookup table and use the formula =A1&B1. Then in your VLOOKUP function use this new column as the lookup column and use a concatination of the two criteria as the criteria. This works very well and is real easy to setup

Good Luck
Mark Graesse
(e-mail address removed)
Our Fair City - Boston M


----- Matt Colon wrote: ----

Good afternoon everyone

I'm looking for a type of function that lets you do a VLOOKUP, but with TW
criteria. I have a spreadsheet with 3 columns - the combination of data i
column A and B for any given row is unique. What I need is to be able t
specify a value to look for in column A, a value to look for in column B
and then return the value in column C for that one corresponding row wher
the criteria was met for both column A & B simultaneously. I have a fairl
crude VBA macro that does this, but I was looking for any advice to see i
this can be done without VBA. Sorting the data is very clumsy because ther
are often over 30,000 rows of data. Any help is greatly appreciated
Thanks

Regard

Matt Colo

 

[Matt Colon]

As I was re-reading my post about the "uniqueness" of the combination of the
2 columns, it came to me - thanks for the confirmations, this will save
hours of work!!

Regards,

MWC

 

[Ken Wright]

With your two lookup values in D1 and D2

=SUMPRODUCT((RngA=D1)*(RngB=D2)*(RngC))

Ranges must be the same length, but no need for a separate column.

 

[Frank Kabel]

With your two lookup values in D1 and D2

=SUMPRODUCT((RngA=D1)*(RngB=D2)*(RngC))

Ranges must be the same length, but no need for a separate column.

Hi Ken
but this will work only if RngC consist of numbers, won't work for text

Frank

 

[Ken Wright]

Very true, but given that the OP wasn't specific, it may be an option, though
admittedly I should have clarified that in my note

 

[Ken Wright]

No argument there from me, but it might be an option. Hard to tell without
specifics.

 

[RagDyer]

This should work *with* text and *without* a helper column!

Lookup value for ColA to be entered in D1,
Lookup value for ColB to be entered in D2,

=INDEX(C1:C100, MATCH(D1&D2,A1:A100&B1:B100,0))

This is an array formula, so must be entered with CSE
(<Ctrl><Shift><Enter>).
If done correctly, the formula will automatically be enclosed in curly
brackets.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


No argument there from me, but it might be an option. Hard to tell without
specifics.

 

[Mark Graesser]

Hi Ken
The more options the better, I always say

Regards
Mark Graesse

----- Ken Wright wrote: ----

No argument there from me, but it might be an option. Hard to tell withou
specifics. :-

 

[Ken Wright]

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/vlookup2.zip
It's in the "Worksheet" section on page:
http://www.bygsoftware.com/examples/examples.htm

Look up on two fields with this alternative to VLOOKUP


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Matt Colon]

Thanks to everyone for all of your suggestions & input!!

Regards,

MWC