SS Lookup

SS Lookup Functions

VLOOKUP

Vertical Lookup

To pull in data from other sheets based on a "key" field (perhaps to create some sort of summary sheet) we can use something like this...

=IFERROR(VLOOKUP(A15, Sheet1!$A$1:$D$84, 3, FALSE),0)

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

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