index and match help! how to set values so that excel knows it's less than/more than?

**Heerah** · 05-26-2012, 12:16 AM

Hi all,
I will be really grateful if someone helps me because I have an excel test this coming Monday :O

Product Quatity Sold "Price
per pair ($)" Total ($) Remarks
Adidas 1000 70 74900
Nike 1300 85 118235
Reebok 600 65 41730
Puma 800 75 64200

So what we're supposed to do is to give a remark based on the total sale:
<=50000 Bad
>50000<=100000 Good
>100000 Excellent

I used index and match: =index(remarkstablebelow, match(74900,whole'total'table), 2)) but it seems excel doesn't recognize 0 as a value and keeps placing 74,900 as bad even though it should be good. Please help, what am I doing wrong ;(
Remarks:
0.00 Bad
50,000.01 Good
100,000.01 Excellent

Once again, thanks soooo much!

**dilipandey** · 05-26-2012, 12:27 AM

Hi Heerah,

Match function syntax has 0/-1/1 as a last syntax which take care of nearest and approximate (greather/less than) match.. explore this as an option.. in case of any issues, upload the sample workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**JTwrk** · 05-26-2012, 12:57 AM

From your post it seems like an if statement is what you want to be using instead of index match. You don't really need to look anything up, instead you want to test for a condition and return a value if that condition is met correct.

Lets say 74,900 is in cell D2

=IF(D2<=50000, "Bad", IF(AND(D2>50000, D2<=100000), "Good", "Excellent"))

instead of hand keying the values in you would instead refer to the cell that contains the values and the same for the remarks

**Heerah** · 05-26-2012, 01:51 AM

Thanks so much for your advice! Am trying out the options and will let you know the results! Thanks SOOOO much ^^

**Heerah** · 05-26-2012, 02:00 AM

Hi Dilipandey,
I'm not too sure what went wrong (i suppose JTwrk is right about me needing an if function mroe) but even after adding those syntax it doesn't work T.T, but JTwrk's method worked! THANKS SO MUCH for your advice and I'll remember them for the test on Monday! ^^

**Heerah** · 05-26-2012, 02:02 AM

Hi JTwrk,
Your method worked like a gem! I never knew that I could have a if function with a if function :O
That is GONNA BE AWESOME for my monday test and I am so so soooo grateful to you and Mr Dilipandey :D
Thanks!!!!!!!!!!

**dilipandey** · 05-26-2012, 02:38 AM

Cheers

Please mark this thread as [SOLVED]...

Regards,
DILIPandey

<click on below 'star' if this helps>

index and match help! how to set values so that excel knows it's less than/more than?

LinkBack

Thread Tools

Rate This Thread

Display

index and match help! how to set values so that excel knows it's less than/more than?

Re: index and match help! how to set values so that excel knows it's less than/more than?

Re: index and match help! how to set values so that excel knows it's less than/more than?

Re: index and match help! how to set values so that excel knows it's less than/more than?

Re: index and match help! how to set values so that excel knows it's less than/more than?

Re: index and match help! how to set values so that excel knows it's less than/more than?

Re: index and match help! how to set values so that excel knows it's less than/more than?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions