Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
ElizaElizaiubi pe Simpatie.ro
 | Femeie
23 ani
Satu Mare
cauta Barbat
23 - 53 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
=INDEX(B:B;MATCH(C1;A:A;0))
Translation of index formula:
- in B:B is the value that you want the formula to return / display
- C1 is the value that you want to look for
- in A:A is where to look for the value in C1
- 0 means "exact match"
or
=VLOOKUP(D1;A:C;3;0)
Why is INDEX-MATCH better than VLOOKUP?
For the VLOOKUP example above, if you insert a column between A and C, the C column where you need VLOOKUP to look into, will become D. The problem is that the formula will look for values in column 3, which will be C, and not D.
_______________________________________
|
|
| pus acum 10 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
Use index - match with multiple conditions:
ex:
=INDEX(D3:D10,MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0))
Info:
_______________________________________
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Very detailed use of INDEX-MATCH:
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Compare data and return "MATCH" "NO MATCH" or "YES" "NO" etc.
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Two Way Lookup (match in column and row from source table)
My requirement was a bit different than the one from source because I needed to extract date from a table (table 2) based on 2 columns (F and G) in my table (table 1).
Table 2 is located in a new book (Book2) as I use it temporarily, just to extract this data from it.
In column I from table 1 I inserted the following formula:
=INDEX([Book1]Sheet1!$A$2:$D$10;MATCH(F469;[Book1]Sheet1!$A$2:$A$10;0);MATCH(G469;[Book1]Sheet1!$A$1:$D$1;0))
Source:
Two way VLookup:
Modificat de TRaP (acum 4 ani)
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
If you want to search for a partial text in range:
So assuming Sheet1!A1:A298 = names and Sheet1!B1:B298 = IDs
And you have Sheet2!A1:A30 = some partial names and you want Sheet2!B1:B30 to return the corresponding IDs.
Maybe this would help to put in Sheet2!B1 and drag down:
=INDEX(Sheet1!$B$1:$B$298;MATCH("*"&A1&"*";Sheet1!$A$1:$A$298;0))
Source:
|
|
| pus acum 4 ani |
|