# Vlookup more than search

#### Jane Collins

##### New Member
=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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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)

Do you mean something like:
Code:
``=IF(\$B\$1=\$B\$91,VLOOKUP(\$B\$1,Open_Percent,D\$83,FALSE),"")``

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),"")

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?

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)

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.

Still a little unclear, but see if the following helps...

Assumptions:

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!

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

Replies
1
Views
163
Replies
8
Views
341
Replies
0
Views
125
Replies
3
Views
424
Replies
2
Views
159

### Forum statistics

1,203,489
Messages
6,055,724
Members
444,814
Latest member
AutomateDifficulty

### 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.

### Which adblocker are you using?

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

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