A sort of HLOOKUP

[Maghiran]

Hello,

I have these 2 Sheets:

\1

and

\1

In the first sheet, in the cell marked ???? I need in the end the minimum value, corresponding to one of the two codes in the table header (values found in sheet2).

For example, in the first row I have value "1" (or 2, or it might be marked with an x) for "258ard" and for "aasd23"; I need a formula that will search for each individual row, for these values - 1 (or 2, or x), return the corresponding value of the codes from sheet2, compare them and display the minimum value.

The number of columns in sheet1 will be constant (the codes are always the same, a limited number) only rows will be added constantly, so I need this per row.

I need some sort of HLOOKUP that returns not only the first value found, but is able to look for multiple entries and return multiple corresponding values.

Thank you

[6StringJazzer]

Welcome to the Forum!

The board accepts attachments in posts. I suggest you simply attach the workbook you used to create these images. Otherwise anyone who is interested in trying to answer your question (like me ) has to look at your images and type in all that data again to duplicate your situation.

It's a lot easier than taking a screenshot, cropping it, uploading it to another site, linking to the site.....

[Maghiran]

Ok, I've attached an xls.

In one row I can have only one of these values combinations:
a. 1
b. 1 twice
c. 2

So I need a formula or more, that looks for one or two "1" or one "2", retrieves the values corresponding to the codes and selects the min.

This time I inserted the line with the values in the same sheet in the last row, since their location is not such a big problem.

You can see in the sheet the formulas I got so far, 2 hlookups, the second one with a "variable" refference, but this only works for 2 or 1 "1". So I would need another hlookup looking for value "2" and a formula to compare 1-3 cells and pick the minimum value, only if the found values are numerical (ignore #N/A)

The MATCH formula must also be looking for "2", not only "1".

I need the formulas to be in such a form that they can be dragged down and keep the correct refferences; for example:

- in row 3 I have this formula - =HLOOKUP(1;B3:$J$10;8;0)
- in row 4 I NEED this formula - =HLOOKUP(1;B4:$J$10;7;0)

an so on, so the Row refference must change as well, in this case from 8 to 7 etc

I will probably copy the values from sheet2 in sheet1 in row 1000 lets say, to have enough rows for entries (I guess 1000 will be enough)

Thank you

[6StringJazzer]

Use this formula in P3. This an array formula. To enter the formula, do not press ENTER, use CTRL-SHIFT-ENTER. If you forget, you can always hit F2 to edit the cell then hit CTRL-SHIFT-ENTER.

=MIN(IF($B3:$J3>0,$B$10:$J$10))

If you enter it correctly, you will see this in the formula box:

{=MIN(IF($B3:$J3>0,$B$10:$J$10))}

Copy down.

[Maghiran]

Thank you

It seems it was so easy, I don't feel very smart right now :D

I knew that the IF formula can't be used for intervals/arrays

[6StringJazzer]

It seems it was so easy, I don't feel very smart right now :D

It always looks easy when you look at the answer without seeing how you got there. I had to try two or three different formulas plus look something up in a web search to solve your problem.

Array formulas are one of the most powerful features of Excel, but in my opinion MS has not provided very good documentation for them. My background is software development and I am used to writing and reading very rigorous specifications about how something works but I guess they figured if they did that with array formulas it would be more confusing then helpful. It has taken me a lot of reading and experimentation to figure out how to use them in various situations, and understand how they behave.
Excel Help gives a very brief description and a couple of examples, but they never really tell you how it works or how to use it. It's like teaching someone to drive a car by saying, "Well, you can use it to go from one place to another."