Searching for a value in Excel, returning yes or no

You could use the following function =IFERROR(IF(MATCH(A1,Sheet1!\$A:\$A,0),”yes”,),”no”) Starting from the inside out Match, looks in sheet1 column A to see if there is … Read more

How to get VLOOKUP to return the *last* match?

You can use an array formula to get data from the last matching record. =INDEX(IF(\$A\$1:\$A\$20=”c”,\$B\$1:\$B\$20),MAX(IF(\$A\$1:\$A\$20=”c”,ROW(\$A\$1:\$A\$20)))) Enter the formula using Ctrl+Shift+Enter. This works like … Read more

Creating a formula to add data together

The requirements are very much unclear. Based on the limited information available, the best solution I can think of is to use query() … Read more

Using VLOOKUP with dynamic range array

A formula like this: =vlookup(A1,Sheet2!A:B,2,0) copied down in your first sheet may serve.

Query reference to several sheets

Instead of just using the ranges, you will have to add the sheet name.. e.g: =ArrayFormula({query(query({‘SHEETNAME’!A3:D,value(‘SHEETNAME’!B3:B)}….ETC

Excel VLOOKUP by second column using table name as range

It’s possible to use OFFSET to return the Table1 range but 1 column over, e.g. =VLOOKUP(“AZ12345”,OFFSET(Table1,0,1),2,FALSE) That will look up AZ12345 in the … Read more

Need help with formula. Do I use XLOOKUP? VLOOKUP? Something else?

You can use XLOOKUP with the ranges “moved” one column, in order to emulate that “offset” you have in your values, like this: … Read more

Function to search columns and tally data

How do I implement a search to continue searching down the column for another instance of the search_key and summate the results paste … Read more

Lookup a value?

I’m a developer with Cognito Forms. Calculations can be used to pull values out of other fields on your Cognito Form, and use … Read more

Fetch value of a cell based on dropdown menu items results on another sheet

paste in B2 cell on Sheet2: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, Sheet1!B4:D4, 3, 0)))