I want vlookup to find a range, and return highest value in that range

**crazie** · 11-04-2008, 03:47 AM

General idea, is that I have the following:
A|B
1|3
2|4
3|5
4|4
5|2

I want vlookup to reference a fixed value (ex. 2), however, I want it to look at all the values in column "A" +/- 50% of that fixed value. So, I want it to look at column "A" between 1 & 3, and return the largest value from column "B" within the specified range.

So if I ask vlookup for 2, I actually want the return to be 5, since it's the largest number within my given parameters.

I am fairly new to this, but I have searched quite a bit and it seems what I'm looking for is a bit unique compared to the examples I've seen.

If I should be looking at VB codes instead, please advise. I have very little experience with VB in excel, but I have played a tiny bit with it.

Thanks for taking the time to read! Any feedback is appreciated.

Anthony

**DonkeyOte** · 11-04-2008, 04:29 AM

assuming your sample values were in A2:B6 the following entered as an array (using SHIFT + CTRL + ENTER)

=MAX(IF($A$2:$A$6>=$D$2*0.5,IF($A$2:$A$6<=$D$2*1.5,$B$2:$B$6)))

Where D2 holds your lookup value (2 in your example)

**crazie** · 11-04-2008, 06:47 PM

Works PERFECTLY!!!!

Much thanks. I think in the end I'm going to need a VB module for what I want, but this was a much needed start.

Can this formula loop through all worksheets in a workbook, regardless of the worksheet names?

If no, then I think I may post this in the VB area.

Thanks again for the FAST and PERFECT response!

**DonkeyOte** · 11-04-2008, 09:06 PM

Can this formula loop through all worksheets in a workbook, regardless of the worksheet names?

I think you'd need to elaborate on this a little bit... are you saying you want to look across multiple ranges over multiple worksheets ?

If so I'd probably say you'll be better off doing for a UDF (which you imply yourself)... I've not looked in programming section so you may have already posted to this effect.

**crazie** · 11-04-2008, 10:20 PM

Here's what I'm doing with this idea:

I'm downloading values directly to excel. Each download starts a new worksheet. The downloads place the data in the exact same place on each worksheet, and labels the worksheet by date and time.

What I want to do, is have a static worksheet, with a list of the lookup values. Then every so often (1 hr for example), I will download data into a new worksheet.

I want excel to auto-populate each column to the right of my lookup value with results corresponding to every new worksheet. (i.e. the first worksheet with data will be the first column next to my lookup values, the 2nd sheet will be the 2nd column to the right of my lookup values, etc.)

So, column A on my static sheet will be my lookup values. Column B will be my results from the first data capture. Column C will be my results from the 2nd data capture....and so on.

Hopefully that makes sense, and hope I didn't overdo the explanation. I haven't posted elsewhere, please let me know if I should...and thanks again for the help.

**DonkeyOte** · 11-05-2008, 03:53 AM

I'm downloading values directly to excel. Each download starts a new worksheet. The downloads place the data in the exact same place on each worksheet, and labels the worksheet by date and time.

Sounds like this is automated with VBA ?

If it is can you post up what you're using -- I would probably advocate just tweaking that routine such that when a new sheet is added the VBA adds the formulae to the master sheet referencing the new sheet. The only other way to do it would be to manually insert the sheet name say in row 1 of each column on the Master sheet -- then use INDIRECT in the array formulae to reference each sheet... INDIRECT is volatile so best avoided where possible... hence the VBA idea -- you can simply create the formula to link to the correct sheet by default on time of creation.

Just to explain the indirect method... continuing prior example -- let's assume on Sheet1 you have the values previously mentioned in A2:B6.

On your Master sheet in A2 you have your criteria value - eg 2.
On your Master sheet in B1 you have the name of the sheet you wish to look at: Sheet1
On your Master sheet in B2 you wish to return the MAX value from Sheet1 col B based on your +/- 50% of A value on Sheet1 col A, so B2 becomes:

Please Login or Register  to view this content.

(entered as array with SHIFT + CTRL + ENTER)

If you added another sheet say called Sheetx you would add Sheetx to C1 on Master Sheet and copy formula from B2 to C2 and it should update.

I reiterate though... if you're using VBA I'd opt for a VBA method of formula insertion disposing of need for volatile function.

**crazie** · 11-05-2008, 01:20 PM

I think it will be done with VBA, but I'm a complete noob at VBA, so it's a rough start trying to get something like this on the first exploration.

I do have someone I work with who knows how to use it, and he's working on the code, but we haven't crossed paths this week. I'll try to get my hands on what he's working with and post up.

Thanks again for all the help. I really appreciate it.

I want vlookup to find a range, and return highest value in that range

LinkBack

Thread Tools

Rate This Thread

Display

I want vlookup to find a range, and return highest value in that range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions