IF : if a figure falls between two others in a long range of possibles

[Wilgoss]

IF this help already exists elsewhere I can’t find it - -apologies, I spent an hour scouring the web

I have a list of people with salaries
I have a list of 40+ salary bands (i.e. 5,000 to 6000, 6,000 to 6,500 etc)
Each salary band has a reference (no)

I need to find out that reference (no) in relation to the salary

So Dave is on 5,600 and I need to know which Band No he falls into basically

Short of having 40+ IF’s in one formula is there a lookup or shortcut?

I would upload an attachment if only I could work out how to get rid of the others I’ve loaded previously because it seems it won’t let me load another!

[mcmahobt]

INDEX/MATCH or VLOOKUP sounds like a good solution for this. However, without seeing how your data is setup, short of showing you the criteria for the functions, not much more can be done. To clear the old workbooks, you could go navigate to "Go Advanced" --> "Manage Attachments" and at the bottom you click the files, then in the upper right corner hit the "X" to delete the file, then upload a clean sample document.

Hope this helps!

[MrShorty]

A properly structured lookup table with one of Excel's lookup functions (VLOOKUP help file: https://support.office.com/en-us/art...rs=en-US&ad=US ) seems to me the easiest solution.

[Wilgoss]

I give up - Thanks and Mery Christmas all!

[mcmahobt]

Unfortunately (at least for me), this is showing up as an invalid attachment. Are you sure you uploaded the file within the attachments for workbooks, and not as an image instead?

[Wilgoss]

Book29.xlsx

Can anyone see this now and perhaps help!?

Cheers

[MrShorty]

Did you look at the help file for the VLOOKUP() function as suggested? This looks like a very basic VLOOKUP() with the fourth argument omitted or TRUE. If you looked at it, was there something you did not understand about the VLOOKUP() function?

[Wilgoss]

Thanks - the help docs are often so confusing, particularly the language used

I wasn't aware you could leave the last field blank to perform such.

Thank you - much apprecitated.

[mcmahobt]

In case you didn't get a working formula, paste this into E4 and drag down:

Please Login or Register  to view this content.