need lookup to display values as they are found

Hi,

I've attached a worksheet which has a hlookup in cell H6. It displays the values assigned to the options in the drop down menus above it, but only does this when all three are completed. Is there a way of getting it to display when they are incomplete?

i.e.

It only shows when all three are selected like "123". When one isn't completed it shows "#NA". if two are selected and the third blank, can it show "12" or if one is selected and two are blank, can it show "1"

Suggestions appreciated

Example.xlsx

Try this:

Formula:

Please Login or Register  to view this content.

thanks for the replies guys.

yours worked fine Glen - thanks!

Hi,

actually this code is returning an "error in value":

=(IF(H2<>"",HLOOKUP(H2,B2:E3,2,FALSE),"")&IF(H3<>"",HLOOKUP(H3,B4:E7,2,FALSE),"")&IF(H4<>"",HLOOKUP(H4,B8:E15,2,FALSE),""))*1

I add in the coloured code to the formula given by Glen above as it wasn't showing as a number and found this trick on another site.

The code works but the cell has the green triangle in the top corner and says it has an "error in value" - it also displays "#VALUE!" when nothing is selected in the boxes. Is this something due to my code I added? I need the box to be blank when nothing's selected....

Try this then:

Formula:

Please Login or Register  to view this content.

It's basically saying:

If all three cells are blank, return a blank.

The rest of the formula was a modification to your original formula. Since you are only trying to return a value if a dropdown value is selected, I added in an if statement to do hlookup only if H2<>"" (meaning H2 is not blank). If H2 is blank, then it will return a blank, resulting a blank & the rest of the formula. So now it can be flexible enough to pick up whether the cell is blank or has a value.

Hi Guys,

Thanks for your feedback!

Glen, that makes perfect sense - actually spent too much time at work today researching this rather than doing what i was suppose to and got my head round the meaning of your coding not long after i posted about it; thanks again - your update works great and there's no error in the value now. fantastic

And thanks newdoverman for your feedback as well - i admit it does look a little weird in it's present form but it's only for this example. If i can get the thing to work as it is now, the really data would make far more sense in the current layout. the values you mentioned were intentional missed - i didnt want to waster my time doing all of them if i could get the first two to work; idea being get the first one going, copy it to the second and if that works, repeat as needed.

I see what you mean about the strange data validation. I did delete them - Yellow and green are not suppose to have a name list after them, but it keeps recreating them for some reason.. odd. Thanks again for your advice

Originally Posted by newdoverman

To get rid of the YELLOW. Go to the Name Manager, click on COLOUR and in the definition in field at the bottom of the dialogue box change the formula to be: =Sheet1!$B$2:$D$2

That will get rid of the YELLOW in the Data Validation list.

To get rid of the GREEN, go to the Name Manager and click on GREEN and click DELETE. That will eliminate the list for GREEN.

It was these strange Data Validation lists that made me think that something was wrong with the example.

Thanks.

I've just taken your advice about vertical listing the nameLists as well. it wasn't working for "Blue" horizontally when it was underneath "red", so i could see it just being a pain later on so did what you recommended. thanks again.

Originally Posted by newdoverman

The problem with the horizontal listing as it is, is that the lookup value ALWAYS looks in the first row of the array definition. The name changes with the choice in H2 but the range doesn't pick up the correct range from which to get a value.

I think that you will find the vertical arrangement easier to work with.

This file shows a possible array in Orange with the formula required in the Orange cell.

I assumed that was the case - It works fine now with the vertical lists and I've added all the name lists just to make sure. Glad you pointed that out as i would have missed it otherwise until it was way down the line and it would of really bugged me.

I've now move on to the cell that's returning the link from the array dependent on the values selected in the drop down list. Its currently saved as a couple of Vlookups inside a hyperlink - one to return the link itself and the other to return the display text. I've got another thread going where I'm asking for advice / pointers on how to figure out getting it to return all the values in the array with the same starting numbers:

ie.


searching for 123456 would find and return:

100000
120000
123000
123400
123450
123456

however searching for 123*** would find and return:

100000
120000
123000
123400
123450
123456

I've looked at countif function but don't think that will work. Problem is i cannot loose the hyperlink function - the whole sheet is set up to return links from an array dependent on your search criteria. It's getting a little puzzling for my novice mind unfortunately. If you have any advice or a suggestion for a subject i should read up on i would appreciate it?

figured as much - thanks for looking though. guess im off to learn basic VBA

Not sure if it's OK to reply to a closed thread, but regarding the multiple matches for the hyperlink, maybe the attachment could be useful.

what does the 1* before the LEFT function do

:the result of "Left" is always text (even if it looks like a number), so multipying by 1 makes it a "real" number without changing the value. "+0" or "-0" or dividing by 1 would have the same result.

Which part of the code determines the "K" in the small function and what does"K" do?

ROWS($1:1) (giving 1 as result) is the K-number (notice the "$" sign), dragging the formula down will give ROWS($1:2) giving 2 as result, etc..
SMALL(Range, 1) will give the smallest number from the range, SMALL(Range, 2) the second smallest, etc..
I suggest you use the "Evaluate formule"button to step through the formula (many times...), this should give you a better understanding of how this sort of formula works. Notice also that it's an Arrayformula, confirmed with ctrl+shift+enter, not just enter.

Thanks Wher,

I've spent most of today looking through this so your comments are helpful.

I'm still trying go figure out why I'm getting #NUM when nothings' selected.

Take a look at the attachment (.jpg of the "Evaluate Formula")
You will notice that in the step right before the #NUM error, the formula tries to determine the smallest (=Small,..,1) number from an array containing no numbers.
This leads to the #NUM error. Similarly formula that would try to determine the third smallest from 2 numbers would lead to a #NUM error.