Request Excel formulas to be generated.(ChatGPT)

Japanese version.

Here is a way to have ChatGPT create Excel formulas for you.

This is useful to use when you have a clear idea of what you want to achieve, but are not familiar with creating Excel formulas.

BGM. Music by mubert.com

Steps

Enter the following in ChatGPT. It is better to clearly indicate the target cell or sheet.

Using the value of A1 as a key, create a formula to retrieve the values in columns A and C of the "List" sheet by searching from column B of the "List" sheet.

Below is the full text of the response.


Here's the formula to retrieve values from columns A and C in the "List" sheet based on the value in cell A1, which is used as a key to search column B in the "List" sheet:

=INDEX(List!$A:$A,MATCH(A1,List!$B:$B,0))&" "&INDEX(List!$C:$C,MATCH(A1,List!$B:$B,0))

This formula uses the INDEX and MATCH functions to retrieve the values from columns A and C in the "List" sheet. The MATCH function searches for the value in cell A1 in column B of the "List" sheet, and returns the row number where the value is found. The INDEX function then retrieves the values in columns A and C from that row. Finally, the "&" operator is used to join the two values together with a space in between.

Since the formulas are not always correct, it is necessary to check that they work. It is also useful to have the formulas presented multiple times, as the formulas may change.

It is also useful to specify the format of the result. In this example, the result is in one cell, but you can specify that it be in another cell.

Using the value of A1 as a key, create a formula to retrieve the values in columns A and C of the "List" sheet by searching from column B of the "List" sheet.The results should be separated by columns.

Here's the formula to retrieve values from columns A and C in the "List" sheet based on the value in cell A1, which is used as a key to search column B in the "List" sheet, and display the results in separate columns:

Enter the following formula in column A:

=IFERROR(INDEX(List!$A:$A, MATCH($A$1, List!$B:$B, 0)), "")

Enter the following formula in column B:

=IFERROR(INDEX(List!$C:$C, MATCH($A$1, List!$B:$B, 0)), "")

This formula uses the MATCH and INDEX functions to retrieve the values from columns A and C in the "List" sheet. The MATCH function searches for the value in cell A1 in column B of the "List" sheet, and returns the row number where the value is found. The INDEX function then retrieves the values in columns A and C from that row. Finally, the IFERROR function is used to display a blank cell if a match is not found.

After entering the formula, the values retrieved from columns A and C in the "List" sheet based on the value in cell A1 will be displayed in columns A and B, respectively.

---

Links

ChatGPT Articles