Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Trip
 
Posts: n/a
Default Vlookup based on two lookup values

Dear All,

How would I write a vlookup type statement that checks against two values,
rather than one. For example I want the formula to check a list for rows that
have the same values as Column A and B, then take the value from column 3 in
the vlookup range. Although the formula below does not work it might make it
a little clearer.

=VLOOKUP(A2 AND B2,OtherSheet!A1:G10,4,FALSE)

Thanks,
Trip
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

I like this syntax:

=index(othersheet!d1:d10,
match(1,(a2=othersheet!a1:a10)*(b2=othersheet!b1:b 10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Trip wrote:

Dear All,

How would I write a vlookup type statement that checks against two values,
rather than one. For example I want the formula to check a list for rows that
have the same values as Column A and B, then take the value from column 3 in
the vlookup range. Although the formula below does not work it might make it
a little clearer.

=VLOOKUP(A2 AND B2,OtherSheet!A1:G10,4,FALSE)

Thanks,
Trip


--

Dave Peterson
  #3   Report Post  
T G Sekhar
 
Posts: n/a
Default

I suggest handling the problem through concatenation.
Create a new column as first column in the table. A1:G10 will become B1:H10.
Use formula A1=B1&"|"&C1
Now the following formula should work
=VLOOKUP(A2&"|"&B2,OtherSheet!A1:H10,5,FALSE)

T G Sekhar


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
how do i get mutiple values using vlookup in excel, lookup value . Abhijeet Excel Discussion (Misc queries) 4 May 19th 05 04:30 AM
Grabbing recods based on date and shift values Jay Excel Worksheet Functions 1 February 25th 05 03:42 PM
Formula to compare multiple rows values based on another column? Murph Excel Worksheet Functions 4 February 21st 05 03:44 AM
project values based on other values Gabriele Excel Discussion (Misc queries) 1 January 7th 05 10:27 AM
If I have X,Y data how do I sum the Y values using a set of bins based on x values ScottBerger Excel Worksheet Functions 1 November 17th 04 12:48 AM


All times are GMT +1. The time now is 01:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"