EXCEL quotation data problem

EXCEL reference data problem

C2 =IF(ISERROR(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))),IF(ISERROR(INDEX(Sheet2!D:D,MATCH(A2,Sheet2! C:C,0))),IF(ISERROR(INDEX(Sheet2!F:F,MATCH(A2,Sheet2!E:E,0))), "No such type", INDEX(Sheet2!F:F, MATCH(A2,Sheet2!E:E,0))),INDEX(Sheet2!D:D,MATCH(A2,Sheet2!C:C,0))),INDEX(Sheet2!B:B,MATCH(A2, Sheet2!A:A,0))) Drop down and copy the formula

or

C2 =IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),IF(ISERROR(VLOOKUP(A2,Sheet2!C:D,2,FALSE)),IF(ISERROR(VLOOKUP (A2,Sheet2!E:F,2,FALSE)), "No such type", VLOOKUP(A2,Sheet2!E:F,2,FALSE)),VLOOKUP(A2,Sheet2!C:D,2,FALSE )),VLOOKUP(A2,Sheet2!A:B,2,FALSE)) Drop down and copy the formula

If column B of SHEET2 already lists the cell location of this category in SHEET1, then the C2 formula should be changed to

=INDIRECT("SHEET1!"&CHAR(COLUMN(INDIRECT(B2)) 65)&ROW(INDIRECT(B2)))

Drop down to copy formula

The formula of B2 of SHEET2 can be written as

=IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),IF(ISERROR(MATCH(A2,Sheet1!C:C,0)),IF(ISERROR(MATCH(A2,Sheet1! E:E,0)),"","E"&MATCH(A2,Sheet1!E:E,0)),"C"&MATCH(A2,Sheet1!C:C,0)),"A"&MATCH( A2,Sheet1!A:A,0)) Drop down and copy the formula

EXCEL about problems encountered during the use of search and reference functions

First deal with the phone numbers of Table A:

Click column E, data, columns, delimiter, next step, check before the comma (if there is no response below, enter a Chinese comma in "Other (O):"), next step, click "Data Preview" For each column under ", set the "Column Data Format" above to text and complete.

In this way, there will be separate phone numbers in columns E and F.

Go to table B and paste the formula =if(iserror(index(table A!D$3:D$30000,match(E2, table A!E$3:E$30000,))),"",index(A Table!D$3:D$30000,match(E2,A table!E$3:E$30000,)))&if(iserror(index(A table!D$3:D$30000,match(E2,A table!F$3:F) $30000,))),"＂,index(A table!D$3:D$30000,match(E2,A table!F$3:F$30000,)))

Change "Table A" in the formula to the real worksheet name, press Enter, then select F2, move the mouse to the lower right corner, and double-click the " ". Finish.

What issues need to be paid attention to when calculating data using cell address references in EXCEL

How to reference data in a certain cell as a reference address in EXCEL

1. This requires the use of the INDIRECT function

2. Definition of INDIRECT function

Returns the reference specified by the text string. This function immediately evaluates the reference and displays its contents. When you need to change a reference to a cell in a formula without changing the formula itself, use the INDIRECT function.

grammar

INDIRECT(ref_text,a1)

Ref_text is a reference to a cell, which can contain an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a text string cell. if

ref_text is not a valid cell reference, and the function INDIRECT returns the error value #REF!.

Note: If ref_text is a reference to another workbook (external reference), that workbook must be open. If the source workbook is not open, the INDIRECT function returns an error value

#REF!.

A1 is a logical value indicating the type of reference contained in cell ref_text.

If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.

If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

3. Example

(1) Suppose you want to write 100 in cell A1 and get the content of cell B100 in cell A2, you can write the formula

in cell A2

=INDIRECT(＂B＂&A1)

(2) Suppose you write "B" in cell A1 and 100 in cell B1. If you want to get the data in cell B100 in cell C1, write in cell C1 formula

=INDIRECT(A1&B1)

(3) Assume that the cell address "B100" is written in cell B1, and the content of cell B100 it represents in cell A1 is to be referenced, then the formula can be written in cell A1

=INDIRECT(B1)

(4) Suppose you want to get a reference to cell B100 in cell A1, then the formula in cell A1 can be written as

=INDIRECT(＂B100＂)

The above is the detailed content of EXCEL quotation data problem. For more information, please follow other related articles on the PHP Chinese website!