VLOOKUP and Nested "OR" Statements 2

[shytott]

Hi
Is it possible to have a nested ‘OR’ statement linked to a VLOOKUP statement? My worksheet has a row for entries between 1 Jan and 31 Dec. Each row has 18 cells of which 6 will contain an “A”,“M”, “N” or “O”. At the moment I have a nested “or” which works but only for one given date. I need to be able to input a date, eg in to cell B2 which then allows the nested “or” statement to work and retrieve the 6 cells for any day in the year. The nested statement I currently use is: =IF(OR(C18="M"),$C$17,IF(OR(G18="M"),$G$17,IF(OR(K18="M"),$K$17,IF(OR(O18="M"),$O$17,IF(OR(S18="M"),$S$17,IF(OR(W18="M"),$W$17,"")))))). This statement is searching row W for the unique occurrence of “M” and then returning the value of Row 17 above said unique occurrence.
I’ve tried adding a Vlookup to the beginning of this formula but Excel doesn’t seem happy with it – ie:
=VLOOKUP($B$2,B18:Z382,IF(OR(C18="M"),$C$17,IF(OR(G18="M"),$G$17,IF(OR(K18="M"),$K$17,IF(OR(O18="M"),$O$17,IF(OR(S18="M"),$S$17,IF(OR(W18="M"),$W$17,"")))))),)

If anyone can fathom what Im trying to do, can you let me know if its possible?

Many Thanks

 

[AnotherHiggins]

What is the purpose of
[tab]OR(C18="M")
?

The syntax for OR is like this:
=OR(Condition1, Condition2)

You can remove every single OR form the formulas without changing anything.

And as for your second formula, the third argument of a VLookup, where you've plugged in your first formula, is what tells the VLookup which column to return. That doesn't seem to be what you want it to do.

I'd suggest you back up and instead of asking for help with formulas that, no offense, you don't seem to understand - just state what you want to achieve.

Use plain English and include sample data and what you would want returned against that sample data.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Based on your description, I would suggest that your initial formula should be something like:

=IF(OR(C18="M",C18="N",C18="O",C18="A"),$C$17,IF(OR(G18="M",G18="N",G18="O",G18="A"),$G$17.....etc etc

Sounds like you need your new statement to

a: Find the row that matches the date you enter in B2
b: check the columns C,G,K,O,S,W in that row for letters A,M,N,O and return the value in the row above if they do

Would that be correct? If not, please, as John requests, tell us what you need to do...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shytott]

Thanks for the response.
Below is a very simplified version of my worksheet with just 3 days data instead of 365. Row 9 (or any row) can have the possible values of M, A, N or O (there will in total be 18 cols of data on the full worksheet). In each row, there will be a unique entry of M,A,N or O. In this instance, I am looking for M.

The expected results from the data below would appear in cell C5 and would be:
1 Jan, DH, 2 Jan IB and 3 Jan DB.
The date to be selected would be entered into B2.

Yes Geoff, almost, it is a case of find the date matching B2, then looking at all the cols in that row and find me the one "M". I'd then have another cell to look for the one "A" and another would search out the one "N" and in each case would return me the column headers of DB, DH or IB. I can achieve excatly what I want to do using the formula in my original posting. All I want to do is be able to select any date and then to return the resultant column headers for that date.

Hope this is clearer
Many Thanks Both



1 A B C D E
2 01-Jan
3
4
5 DH
6
7
8 DB DH IB
9 01-Jan N M A
10 02-Jan A N M
11 03-Jan M A N

 

[SkipVought]

hi,

Name your Date Range Dte.
Name your DB, DH, IB lookup range ReturnRange

Enter the M N O A value you are searching for in B3.
Enter the lookup date in B2

Here's the formula in B5...
[tt]
B5: =INDEX(ReturnRange,1,MATCH(B3,OFFSET(B8,MATCH(B2,Dte,0),1,1,3),0))
[/tt]
VOLA!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I added some additional Named Ranges for clarity and also a necessary COUNTA function. This way, you can have as many ReturnRange values as you need, and the formula will adjust accordingly.

Name your Date Range Dte.
Name your DB, DH, IB lookup range ReturnRange

Enter the M N O A value you are searching for in B3. Name it LookupValue
Enter the lookup date in B2. Name it LookupDate

The table starts in B8...
[tt]
Dte DB DH IB
1-Jan N M A
2-Jan A N M
3-Jan M A N
[/tt]

Here's the formula in B5...
[tt]
B5: =INDEX(ReturnRange,1,MATCH(LookupValue,OFFSET(B8,MATCH(LookupDate,Dte,0),1,1,COUNTA(ReturnRange)),0))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shytott]

Cheers Guys
Thats got me going in the right direction