SS Lookup

The IFERROR enables us to return 0 if the lookup failsIn this example cell A15 on our current sheet holds the key to lookup on Sheet1Using $ symbold in front of the range of cells to scan makes it possible to drag the formula up/down in order to easily do lookups on multiple values.The range must include the cells likely to hold the key field and the cells likely to hold the return valueIn our example the 3 defines that the cell in the 3rd column on the same rows as the lookup key will be returned.FALSE specifies that an error is returned if the lookup would return more than one result.

HLOOKUP

Horizontal Lookup

XLOOKUP

INDEX

MATCH

XMATCH

EXACT

=EXACT(A1,B1)

TRUE if contents of specified cells are an exact match, otherwise FALSE

FILTER

=FILTER(B2:B8,C2:C8=B18,D2:D8=C18)

Find the Value from Range B2:B8 where Range C2:C8 matches value in B18 and Range D2:D8 matches value in C18

Troubleshooting

No matches are found in FILTER evaluation.

In this example I am trying to return the CVSS Score (held in Column F of the CVE tab) that matches a CVE number (Column A of the CVE tab) and product identifier (Column AG of CVE tab).

See the "Versions" and "CVE" tabs in my "MySQL-Key-Dates" spreadsheet if you wish to explore this example further.

=FILTER(CVE!F:F,CVE!A:A=C174,CVE!AG:AG="NDB")

Sometimes the correct value is returned but sometimes we get #N/A with the "No matches are found in FILTER evaluation." error. The CVE number definitely exists for the product identifier selected. To Troubleshoot this I used this formula to see if the CVE number really matched...

=ARRAYFORMULA(JOIN("-", CODE(MID(C174, SEQUENCE(LEN(C174)), 1))))

For CVE‑2025‑21575 This returned...

67-86-69-8209-50-48-50-53-8209-50-49-53-55-53

When I tried a similar thing on the CVE tab, for CVE-2025-21575 this returned..

67-86-69-45-50-48-50-53-45-50-49-53-55-53

So, the root cause is the hyphens. Through some anomaly with the way the cells were populated, one cell has a regular Hyphen-Minus and one has a Non-Breaking Hyphen. I corrected the cells and the error disappeared.

MySQL-Key-Dates

Bibliography

VLOOKUPhttps://support.microsoft.com/en-au/office/video-vlookup-when-and-how-to-use-it-9a86157a-5542-4148-a536-724823014785https://support.microsoft.com/en-gb/office/look-up-values-with-vlookup-index-or-match-68297403-7c3c-4150-9e3c-4d348188976bhttps://www.ablebits.com/office-addins-blog/if-vlookup-excel/https://www.ablebits.com/office-addins-blog/vlookup-multiple-values-excelhttps://www.ablebits.com/office-addins-blog/2020/02/12/vlookup-multiple-sheets-excel-examples/https://www.solveyourtech.com/display-0-instead-na-using-vlookup-excel-2013/https://www.extendoffice.com/excel/functions/excel-vlookup-function.htmlhttps://www.indeed.com/career-advice/career-development/vlookup-multiple-valueshttps://www.quora.com/Can-you-have-multiple-criteria-for-a-VLookup-formula-in-Microsoft-Excelhttps://blog.coupler.io/excel-vlookup-multiple-columns/https://www.exceldemy.com/excel-vlookup-last-value-in-column/https://superuser.com/questions/781992/how-to-get-vlookup-to-return-the-last-matchhttps://support.google.com/docs/answer/3093318
HLOOKUPhttps://support.microsoft.com/en-gb/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f
XLOOKUPhttps://support.microsoft.com/en-gb/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
LOOKUPhttps://www.exceldemy.com/excel-vlookup-last-value-in-column/
XMATCHhttps://exceljet.net/articles/index-and-match
EXACThttps://support.microsoft.com/en-gb/office/exact-function-d3087698-fc15-4a15-9631-12575cf29926
INDEX/MATCHhttps://exceljet.net/articles/index-and-matchhttps://www.exceldemy.com/excel-vlookup-last-value-in-column/
QUERYhttps://stackoverflow.com/questions/16256388/recreating-a-google-spreadsheets-query-in-excel

Page updated

Google Sites

Report abuse