Forum Discussion
Multiple search criteria
i have 3 Validation lists one for the Yellow, one for the Red and one for the Green area. I need to indeks or lookup theese 3 criteria so i get a singular result from the blue are.
I cant figure out to how to combine the red area and the green to form a column number i can use in either an indeks formula or a lookup formula
Please help 🙂
4 Replies
Hi,
interesting problem.
Yes so if you have the correct column then its no problem to use a index match to find the reactance in the blue area.
If your header Kolonne1 = A1, Kolonne2 = B1, ..
And then this is given
cable type = NOBH = B21
# leaders = 4-leder = B22
Check every column for a TRUE or #N/A
C25=(MATCH($B$21;(B2:B9);0) * MATCH($B$22;B10;0) > 0)
D25 =(MATCH($B$21;(C2:C9);0) * MATCH($B$22;C10;0) > 0)
E25=(MATCH($B$21;(D2:D9);0) * MATCH($B$22;D10;0) > 0)
...
Then you check in which column you have a TRUE and you have the column nbr.
=MATCH(TRUE;C25:I25;0)
Hope this helps
I think we need at least two things from you in order to help.
- Please upload/post a copy of your actual spreadsheet. The image is of limited value, whereas the actual spreadsheet can be used to try out different formulas.
- If you can, please spell out an actual example of what you mean by "combine the red and green to form a column number" ... that's at best ambiguous now.
I am assuming that those validation lists you refer to will be clear once you've uploaded your actual spreadsheet, but if that's not the case....please spell that out a bit more as well.
Finally, if you could describe what the context is here: i.e., what's it all about? What does these letters and numbers represent? It may seem unnecessary that we know that, but the more we can understand of the "big picture" the more we might be able to make sense of it all.
mathetes To set things straight, this is a lookup table for the reactance of an installation cable.
The validation lists are just dropdown lists to choose from, where for example "NOIKLX" is the cable type and "3-leder" is in this example a cable with 3 conductors. Together they form a unique "product".
In this case "NOIKLX" appears in 3 columns and "3-leder" shall be used to narrow it down to 1 coulmn.
the column under "mm2" is the cross section of the cables and is going to used as a row lookup
Together theese 3 criteria should form one result.
Hope this gives an idea of its usage.
currently i have worked around this issue by hardcoding the column numbers using the Case method in VBA but i would like a more simple approach which could later be converted into a VBA code
I'm sure this is frustrating to you, because in your mind it's all crystal clear. But I'm still having to do some guessing "Is this what you mean?"
So, is this what you mean? =VLOOKUP("NOIKLX"&"3-leder",$XY$1:$XZ$50,2,0)
Somehow, I doubt that's what you mean. You seem more sophisticated than to need help with text concatenation.
And where, pray tell, is the table where INDEX/MATCH or VLOOKUP are to do their matching or looking up? (My use of $XY$1:$XZ$2 in that hypothetical formula was just a filler for where such a table might be, just to be clear.)
It would help me, if not others, if you'd spell out a complete example. As in, "I want to combine NOKLX with 3-leder with whatever, in order to create a reference that is then matched with a cell in such and such a table, with the result being............" And then show that table where the lookup is being performed.
And, again, my first request was that you actually upload the spreadsheet, so we could see and use what you're working with; otherwise we are (or at least I am) forced to do our best guess.
