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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)
 
Upvote 0
Do you mean something like:
Code:
=IF($B$1=$B$91,VLOOKUP($B$1,Open_Percent,D$83,FALSE),"")
 
Upvote 0
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),"")
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top