Learn how to LOOKUP the information you wish to have to search out in an Excel vary

In mobile G13 within the representation “Helper Column Added”, I’ve applied the next components:

=LEFT(F13,IFERROR(FIND(“-“,F13),FIND(“+”,F13))-1)*1

Now not my longest calculation ever in anyway, nevertheless it nonetheless wishes explaining. All of it centres at the FIND serve as:

FIND(“-“,F13)

This components seems for the nature place of the hyphen (“-“) in mobile F13. Within the textual content string “0-19” (the contents of mobile F13), the hyphen is obviously the second one persona within the textual content string, so FIND(“-“,F13) will go back the worth two (2).

If there is not any hyphen, the mistake message #VALUE! is returned as an alternative. Therefore, we “wrap” this components in an IFERROR expression as follows:

IFERROR(FIND(“-“,F13),FIND(“+”,F13))

IFERROR evaluates FIND(“-“,F13). If it returns a numerical price, that’s effective; if it returns an error, it as an alternative searches for a plus (“+”) image in the similar mobile:

FIND(“+”,F13)

That is required on account of the worth in mobile F20, ie, “85+”. Right here, FIND(“-“,F20) would go back #VALUE!, however FIND(“+”,F20) — the components to calculate if the main calculation ends up in an error — would go back 3 (3), because the plus image is the 3rd persona on this textual content string.

Subsequently, the components:

=LEFT(F13,IFERROR(FIND(“-“,F13),FIND(“+”,F13))-1)

will go back the textual content identical of the primary quantity within the vary for all buckets. For instance, in row 20, IFERROR(FIND(“-“,F13),FIND(“+”,F13)) returns 3 (3), so the components resolves to:

=LEFT(F20, 3 – 1)

which might go back the 2 “left-most” characters within the textual content string “85+”, ie, “85”. Alternatively, this may no longer be a numerical price, so multiplying by way of one (1) converts those textual content strings into numerical values, therefore the overall components:

=LEFT(F13,IFERROR(FIND(“- “,F13),FIND(“+”,F13))-1)*1

This has now addressed our first factor. We have a numerical check in (a listing of values in expanding order) we would possibly use to appear up our scholars’ marks. I now want to imagine which serve as to make use of to appear up the information.

As with any issues in Excel, most simple is absolute best. Sure, we’ve got VLOOKUP, HLOOKUP, INDEX MATCH, OFFSET MATCH, SUMIF, SUMIFS, SUMPRODUCT, XLOOKUP, et al., however on every occasion you could have information in ascending order in a spreadsheet, your most simple, maximum dependable serve as to make use of is not anything greater than the extraordinarily humble LOOKUP serve as.

LOOKUP reminder

LOOKUP has two paperwork: an array shape and a vector shape. Let me give an explanation for the jargon:

• An array is a choice of cells consisting of no less than two rows and no less than two columns.
• A vector is a choice of cells throughout only one row (row vector) or down only one column (column vector).

The diagram “Array and Vectors” (beneath) must be self-explanatory.