Vlookup more than search

Jane Collins

New Member
Joined
Jul 22, 2005
Messages
17
=VLOOKUP($B$1,Open_Percent,D$83,FALSE)

I need to change the above formula to also include the look up of cell $B91.

Something like this:

=VLOOKUP($B91,$B$1,Open_Percent,D$83,FALSE) but it doen't work.

I want to check that both B91 & B1 match the criteria in Open_Percent (range) and return value that matches D83 column number.

Is there a solution?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,356
Office Version
  1. 365
Platform
  1. Windows
I reckon there is, but can you explain in more detail what you mean by:
I want to check that both B91 & B1 match the criteria in Open_Percent (range)
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Do you mean something like:
Code:
=IF($B$1=$B$91,VLOOKUP($B$1,Open_Percent,D$83,FALSE),"")
 

Jane Collins

New Member
Joined
Jul 22, 2005
Messages
17
No this it not the formula I was after.

Cell B91 (Venue) and B1 (Ritzs), I want to look up both these in Open_Percent and then retun the value D83 being the column number.

Normal lookup formulas only allow you to check one criteria, i want something like this

take $B$1 and B$91 then LOOKUP($B$1 and b91,Open_Percent,D$83,FALSE),"")
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Are the 2 lookup values to be concatenated as in

1st=part
2nd=two

search target="part two"

Or is it that you wish to return a value where one column matches the 1st value and another column matches the 2nd?
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Jane, can you give us a clue as to the structure of Open_Percent, how many columns etc. and against what do you expect to compare B91 and B1, is it, as Jon suggests, in a concatenated form against a cell in just one column of Open_Percent or do you want to compare each one separately against cells in two columns and return a result from another column when both B91 and B1 match their criteria on a row.

Does the following help?
Book1
ABCDEFG
1Formula CriteriaVenueRitzsWorkingPrice
2Venue :AA1A115
3Ritz:3B2B220
4Col:2A3A325
5D4D430
6FormulaA5A535
7Price:25F6F640
8
9Named range F1:G7
10
11Col F is Additional Working Column inserted to concatenate cols D&E
12
13
14
Sheet1


The formula in B7 is : =VLOOKUP(B2&B3,Open_Percent,B4,FALSE)
 

Jane Collins

New Member
Joined
Jul 22, 2005
Messages
17

ADVERTISEMENT

Thanks you for you reply but It's not want I need. Criteria as follows:

B1 = Ritz from a list drop down box on sheet 1 which is pulling from B56

Still in sheet 1 here is where the formula is in column C:

Row 90 B C
Row 91 0 0
1 0
2 0
3 0
4 0


Open_Percent on sheet 2 (Range Named)
Column C D E
Ritz 0 0
Ritz 1 5
Ritz 2 10
Ritz 3 0
Ritz 4 25
Total - Ritz 40

This is where I need to lookup on sheet 1 two types of criteria that match the criteria in the named range.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,224
Office Version
  1. 365
Platform
  1. Windows
Still a little unclear, but see if the following helps...

Assumptions:

A1:E5 contains your table

Column A contains a name, such as Ritz

Column B contains the venue

H1 contains your first lookup value, such as Ritz

I1 contains your second lookup value

J1 contains the column index

Formula:

=INDEX(A1:E5,MATCH(1,(A1:A5=H1)*(B1:B5=I1),0),J1)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the references accordingly.

Hope this helps!
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Trying to follow your layout, but still unsure of what you are trying to do, but in Sheet2, C91 try:
Code:
=IF($B$1=INDEX(Open_Percent,MATCH(B91,Sheet2!$D$1:$D$5,0),1),INDEX(Open_Percent,MATCH(B91,Sheet2!$D$1:$D$5,0),3),"")
which you can copy down.

Edit: I put the named range "Open_Percent" on Sheet 2 as you stipulated, in columns C, D & E on rows 1 to 5.

HTH
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,146
Members
430,342
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top