For anyone experiencing issues in a big data range, ensure you dont have any cells existent with "#N/A" or it will break it. Simple error but can be overlooked.

Hello, I want to use keyword instead of specific text or value here for multiple return values, can you please share code formula for same.

Hello - This formula worked perfectly - thank you! However, I want to apply it to a long list of lookup values. When I copy and paste the formula it obviously returns the same results as the first lookup value so I removed the $'s from $A$11, but that changes the result to something incorrect. How can I quickly apply this same formula for a long list of lookup values?

Thank you!

M

Bonjour,

J'ai retransposé mon problème exactement dans les mêmes positions de colonne que l'exemple et j’obtiens le message #VALEUR! dès la première case.

Mon excel fonctionne en francais, j'ai tout retranscrit en francais, passé ";"au lieu de ",", appuyé sur CTRL+MAJ+Entrée... des idées sur l'origine du problème?

Fanny

what you want to put the price in order of highest qty from column c?

i have to find vertical value first, in vertical value there are more horizontal value and i have choose grater value which i have. which function or formula can help

Hi. I have three google spreadsheet. First sheet is named "Summary. It is a table containing the name of our employee and number of evaluation in a week from 1-7. The next sheet is named "Chat Score"and third sheet is named Ëmail Score". What I want to to do is when we input a evaluation score to one of our employee it will be automatically recorded on the "Summary sheet" as evaluation 1 or 2 and so on and so forth. In short I want to get multiple return value in a single seach key across multiple sheet. I hope you can help. https://docs.google.com/spreadsheets/d/1lt-e4MxddUKg5xDt_0YchBiEgGe4mFKB-dHENwwtA6Y/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1lt-e4MxddUKg5xDt_0YchBiEgGe4mFKB-dHENwwtA6Y/edit?usp=sharing

Although I'm looking up my values in another worksheet (I don't think this should cause a problem?), I only seem to have success dragging to the right if I go in and change the +1 to '0' for the 1st instance of the lookup ID, '1' for the 2nd instance and so on. I know how many instances of the lookup ID I have so I can workaround. What do I need to change to allow me to successfully drag the fx across horizontally without needing to edit (i.e getting the #NUM to appear once there are no more lookup values to return).

How to List all values(like list of groceries bought) caused by a specific value (by a person X) using VLOOK_UP and other possible formulas

Hello, i type the formula and excel gives an error that says that the formula does not have enough arguments, any help?

Just a quick note to all, if you change it to be:

=IFERROR(INDEX($B$2:$B$9, SMALL(IF($A$11=$A$2:$A$9, ROW($A$2:$A$9)-ROW($A$2)+1), COLUMN(A1))),"None")

The #NUM will become none or use "" to get a blank cell! Looks much tidier!

I have a similar problem but different.

Vlookup Multiple Values - Return MULTIPLE corresponding values for MULTIPLE Lookup Values

The formula I have searches for words in a text strings, starting with A1, then adds categories from a large list of categories in a table on ANOTHER WORKSHEET 'Dynamic Categories Lists' , depending on the words found in the A1 string. This is formula is in B1. The amount of data is huge 19,000 text strings in row A.


For examples the text string might say:
A B C
1 dog has black spots Dalmation
2 dog is tall Large Dog

My formula searches for "black spots" and returns " Dalmatians " to B1
My formula searches for " dog is tall" - my formula searches " tall " and return " large dogs" to B2

Formula in B1 is:
=PROPER(IFERROR(LOOKUP(1E+100,SEARCH('Dynamic Categories Lists'!$A$1:$A$1000,A1),'Dynamic Categories Lists'!$A$1:$A$1000),""))

'Dynamic Categories Lists' (DIFFERENT WORKSHEET)
A B
1 Search Word to Find Categories: List Paste
2 black spots Dalmation
3 tall Large Dog
4 short Small Dog
5 -1000 MORE -1000 MORE


My problem is I need to find the 2nd, 3rd, 4th occurrences

Example
A B C D
1 dog has black spots Dalmation
2 dog is tall Large Dog
3
4 dog has black spots and is tal Dalmation Large Dog


A4 "dog has black spots and is tall" I want the formula to return "Dalmation" & "large dog" to B3

Any help would be appreciated. I have searched heaps of threads and haven’t been able to find the answer!

Thanks for your tips on

I have a similar problem but different.

Vlookup Multiple Values - Return MULTIPLE corresponding values for MULTIPLE Lookup Values

The formula I have searches for words in a text strings, starting with A1, then adds categories from a large list of categories in a table on ANOTHER WORKSHEET 'Dynamic Categories Lists' , depending on the words found in the A1 string. This is formula is in B1. The amount of data is huge 19,000 text strings in row A.


For examples the text string might say:
A B C
1 dog has black spots Dalmation
2 dog is tall Large Dog

My formula searches for "black spots" and returns " Dalmatians " to B1
My formula searches for " dog is tall" - my formula searches " tall " and return " large dogs" to B2

Formula in B1 is:
=PROPER(IFERROR(LOOKUP(1E+100,SEARCH('Dynamic Categories Lists'!$A$1:$A$1000,A1),'Dynamic Categories Lists'!$A$1:$A$1000),""))

'Dynamic Categories Lists' (DIFFERENT WORKSHEET)
A B
1 Search Word to Find Categories: List Paste
2 black spots Dalmation
3 tall Large Dog
4 short Small Dog
5 -1000 MORE -1000 MORE


My problem is I need to find the 2nd, 3rd, 4th occurrences

Example
A B C D
1 dog has black spots Dalmation
2 dog is tall Large Dog
3
4 dog has black spots and is tal Dalmation Large Dog


A4 "dog has black spots and is tall" I want the formula to return "Dalmation" & "large dog" to B3

Any help would be appreciated. I have searched heaps of threads and haven’t been able to find the answer!

I have a spreadsheet with 115 client names in it that I pull values on each day from one tab to another using a vlookup. Another person in the office updates the values each day by pasting new values along with the corresponding client names. Client names are pasted on the 'PVAL' tab in column A and client values are pasted in column B. On the tab named Master we run a vlookup for the client names starting in cell N6 (the matching client name is listed in cell AB6). The vlookup is =VLOOKUP(AB6,PortDescPortVal,2,FALSE) Every day we update the spreadsheet there are the same 10 client names that will not automatically update. I have to go to the PVAL tab everyday and retype the client names for those 10 and then the data will update. It works for the rest of the clients every day. Also note that the person who updates the data has tried retyping their names for me but for some reason when she retypes their names the lookup still won't work. I can't find a setting in her Excel that differs from mine. I have ensured that all of the client names are in the array. I cannot for the life of me figure out what the problem is here and why I have to retype 10 out of 115 client names each and every day for the lookup to work. HELP!