Menu

Vlookup for multiple Lookup_value

Search this Thread

Prev

Previous Post

Next Post

Next

#2

Report Post

Posted to microsoft.public.excel.programming

smartin

smartin is offline

external usenet poster

Posts: 915

Default

Vlookup for multiple Lookup_value

wrote:
Hi friends,

Could anybody suggest me if i serching for more than single lookup
value how will be the vlookup formula?

For e.g

If i need Lookup value A with combination of 11 and 12 which are in
another two diff.columns and i need those two as well. So, can i put
three criterias to get very specific output?

Thanks in Advance for ur help freind !

Yogi

Hi Yogi, VLOOKUP cannot do this, but there are ways.

If I understand you correctly your data looks something like this:

column: A B C D
data: A 11 12 Target

and you wish to obtain column D (Target).

If Target is numeric, SUMPRODUCT works well:

=SUMPRODUCT(--(J1=$A$1:$A$99),--(K1=$B$1:$B$99),--(L1=$C$1:$C$99),$D$1:$D$99)

J, K and L contain your search terms, e.g., "A", 11, and 12, or you can
go without the references and hard-code the search terms. $99 is an
arbitrarily large row reference to contain the lookup area.

If Target might not be numeric, a different approach is necessary. The
following must be entered as an array* formula:

=INDEX($D$1:$D$99,MATCH(1,(J1=$A$1:$A$99)*(K1=$B$1 :$B$99)*(L1=$C$1:$C$99),0))

*Commit the array formula by pressing Ctrl+Shift+Enter, do not just
press Enter or Tab.

Hope this helps.

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VLOOKUP on partial lookup_value	Faraz A. Qureshi	Excel Discussion (Misc queries)	8	June 12th 09 05:57 AM
vlookup lookup_value text	natk	Excel Worksheet Functions	3	May 5th 09 07:54 PM
vlookup lookup_value	ATIHelp	Excel Worksheet Functions	5	July 18th 07 07:12 AM
Vlookup(lookup_value)	TSulit	Excel Worksheet Functions	1	February 22nd 07 04:23 PM
Update Vlookup lookup_value	Phil Floyd[_2_]	Excel Programming	2	February 15th 05 03:48 AM

All times are GMT +1. The time now is 06:58 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement