ExcelBanter - Lookup function still not working correctly

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Lookup function still not working correctly (https://www.excelbanter.com/excel-worksheet-functions/37972-lookup-function-still-not-working-correctly.html)

Mike K

Lookup function still not working correctly

Oh wise ones,
I thought I had it, I really thought I had it this
time. Ok, so I have my data as follows;

B2 =Today() which as of right now is Sunday
E2 =Lookup(Text(B2,"ddddd"),C7:I7,C8:I8) vector based
C7:I7 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
C8:I8 C A A C C A A

If B2 is "Sunday" and I7 contains "Sunday", should'nt E2 return "A" from I8?
Why is it returning "C"?

The equation builder shows "Sunday" for the lookup value. So I'm not sure
whats wrong. I know B2 is really a whole number, but I thought the "TEXT"
function in E2 was accounting for that. Please advise

Thanks, Mike

Biff

Hi!

Hey, I remember this!

I don't know why that is happening!

If you try this:

=TEXT(B2,"ddddd")=I7

It returns TRUE which means the lookup value matches SUNDAY in I7. ???

Here's a different formula:

=INDEX(C7:I7,MATCH(TEXT(B2,"ddddd"),C7:I7,0))

Biff

"Mike K" wrote in message
...
Oh wise ones,
I thought I had it, I really thought I had it this
time. Ok, so I have my data as follows;

B2 =Today() which as of right now is Sunday
E2 =Lookup(Text(B2,"ddddd"),C7:I7,C8:I8) vector based
C7:I7 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
C8:I8 C A A C C A A

If B2 is "Sunday" and I7 contains "Sunday", should'nt E2 return "A" from
I8?
Why is it returning "C"?

The equation builder shows "Sunday" for the lookup value. So I'm not sure
whats wrong. I know B2 is really a whole number, but I thought the "TEXT"
function in E2 was accounting for that. Please advise

Thanks, Mike

Biff

Ooops!

The new formula should be:

=INDEX(C8:I8,MATCH(TEXT(B2,"ddddd"),C7:I7,0))

Biff

"Biff" wrote in message
...
Hi!

Hey, I remember this!

I don't know why that is happening!

If you try this:

=TEXT(B2,"ddddd")=I7

It returns TRUE which means the lookup value matches SUNDAY in I7. ???

Here's a different formula:

=INDEX(C7:I7,MATCH(TEXT(B2,"ddddd"),C7:I7,0))

Biff

"Mike K" wrote in message
...
Oh wise ones,
I thought I had it, I really thought I had it this
time. Ok, so I have my data as follows;

B2 =Today() which as of right now is Sunday
E2 =Lookup(Text(B2,"ddddd"),C7:I7,C8:I8) vector based
C7:I7 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
C8:I8 C A A C C A A

If B2 is "Sunday" and I7 contains "Sunday", should'nt E2 return "A" from
I8?
Why is it returning "C"?

The equation builder shows "Sunday" for the lookup value. So I'm not sure
whats wrong. I know B2 is really a whole number, but I thought the "TEXT"
function in E2 was accounting for that. Please advise

Thanks, Mike

Rowan

I can't say why this isn't working for you, it seems ok to me. How about
trying:

=HLOOKUP(TEXT(B2,"ddddd"),C7:I8,2,0)

Hope this helps
Rowan

"Mike K" wrote:

Oh wise ones,
I thought I had it, I really thought I had it this
time. Ok, so I have my data as follows;

B2 =Today() which as of right now is Sunday
E2 =Lookup(Text(B2,"ddddd"),C7:I7,C8:I8) vector based
C7:I7 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
C8:I8 C A A C C A A

If B2 is "Sunday" and I7 contains "Sunday", should'nt E2 return "A" from I8?
Why is it returning "C"?

The equation builder shows "Sunday" for the lookup value. So I'm not sure
whats wrong. I know B2 is really a whole number, but I thought the "TEXT"
function in E2 was accounting for that. Please advise

Thanks, Mike

Biff

Another alternative:

=HLOOKUP(TEXT(B2,"ddddd"),C7:I8,2,0)

Biff

"Biff" wrote in message
...
Hi!

Hey, I remember this!

I don't know why that is happening!

If you try this:

=TEXT(B2,"ddddd")=I7

It returns TRUE which means the lookup value matches SUNDAY in I7. ???

Here's a different formula:

=INDEX(C7:I7,MATCH(TEXT(B2,"ddddd"),C7:I7,0))

Biff

"Mike K" wrote in message
...
Oh wise ones,
I thought I had it, I really thought I had it this
time. Ok, so I have my data as follows;

B2 =Today() which as of right now is Sunday
E2 =Lookup(Text(B2,"ddddd"),C7:I7,C8:I8) vector based
C7:I7 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
C8:I8 C A A C C A A

If B2 is "Sunday" and I7 contains "Sunday", should'nt E2 return "A" from
I8?
Why is it returning "C"?

The equation builder shows "Sunday" for the lookup value. So I'm not sure
whats wrong. I know B2 is really a whole number, but I thought the "TEXT"
function in E2 was accounting for that. Please advise

Thanks, Mike

Mike K

Thanks Biff, Rowan.

I got them both to work correctly.

Thanks again,
Mike

"Biff" wrote:

Another alternative:

=HLOOKUP(TEXT(B2,"ddddd"),C7:I8,2,0)

Biff

"Biff" wrote in message
...
Hi!

Hey, I remember this!

I don't know why that is happening!

If you try this:

=TEXT(B2,"ddddd")=I7

It returns TRUE which means the lookup value matches SUNDAY in I7. ???

Here's a different formula:

=INDEX(C7:I7,MATCH(TEXT(B2,"ddddd"),C7:I7,0))

Biff

"Mike K" wrote in message
...
Oh wise ones,
I thought I had it, I really thought I had it this
time. Ok, so I have my data as follows;

B2 =Today() which as of right now is Sunday
E2 =Lookup(Text(B2,"ddddd"),C7:I7,C8:I8) vector based
C7:I7 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
C8:I8 C A A C C A A

If B2 is "Sunday" and I7 contains "Sunday", should'nt E2 return "A" from
I8?
Why is it returning "C"?

The equation builder shows "Sunday" for the lookup value. So I'm not sure
whats wrong. I know B2 is really a whole number, but I thought the "TEXT"
function in E2 was accounting for that. Please advise

Thanks, Mike

Biff

You're welcome. Thanks for the feedback!

Biff

"Mike K" wrote in message
...
Thanks Biff, Rowan.

I got them both to work correctly.

Thanks again,
Mike

"Biff" wrote:

Another alternative:

=HLOOKUP(TEXT(B2,"ddddd"),C7:I8,2,0)

Biff

"Biff" wrote in message
...
Hi!

Hey, I remember this!

I don't know why that is happening!

If you try this:

=TEXT(B2,"ddddd")=I7

It returns TRUE which means the lookup value matches SUNDAY in I7. ???

Here's a different formula:

=INDEX(C7:I7,MATCH(TEXT(B2,"ddddd"),C7:I7,0))

Biff

"Mike K" wrote in message
...
Oh wise ones,
I thought I had it, I really thought I had it this
time. Ok, so I have my data as follows;

B2 =Today() which as of right now is Sunday
E2 =Lookup(Text(B2,"ddddd"),C7:I7,C8:I8) vector based
C7:I7 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
C8:I8 C A A C C A A

If B2 is "Sunday" and I7 contains "Sunday", should'nt E2 return "A"
from
I8?
Why is it returning "C"?

The equation builder shows "Sunday" for the lookup value. So I'm not
sure
whats wrong. I know B2 is really a whole number, but I thought the
"TEXT"
function in E2 was accounting for that. Please advise

Thanks, Mike

All times are GMT +1. The time now is 06:37 AM.