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?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,483
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
19,402
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,226
Messages
5,570,990
Members
412,353
Latest member
SofiaV
Top