Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up Function
What function do I use if I have two spreadsheets and I want the master
spreadsheet to look up data on a second spreadsheet, if the data is on the master I need it to make the record. Also, what options do I have to "mark" the record, highlight row, change the font color, add a charachter to another cell at the end of the row, etc.? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up Function
Hi Whitney,
For a simple method, try using LOOKUP, VLOOKUP or HLOOKUP. The formula wizard should help you out. However these rely on the data you are looking up being sorted in alphabetical order. If you'd like something more robust (but a bit more complicated) try a combination of MATCH and INDEX. MATCH will find the data you are after (regardless of whether it's sorted) but only returns a row number. INDEX will then find the correct data you are after by using that row number. For example =INDEX(result range,MATCH(lookup value,lookup range,0)) As for "marking" the record, go to the Format menu and choose Conditional Formatting. You can then set the Format of the current cells based on what's in the current cells or even based on other cells. It might take a bit of playing around to get it to do exactly what you want though (especially the subtle differences between relative and absolute references) - Atreides |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up Function
I guess I just need it to say Yes or No. I would liket Mastersheet to look at
Sheet2 and just say Yes if on the sheet and leave blank if not on the sheet Mastersheet: A B Order ID Result (Y) Sheet2 A Order ID "Atreides" wrote: Hi Whitney, For a simple method, try using LOOKUP, VLOOKUP or HLOOKUP. The formula wizard should help you out. However these rely on the data you are looking up being sorted in alphabetical order. If you'd like something more robust (but a bit more complicated) try a combination of MATCH and INDEX. MATCH will find the data you are after (regardless of whether it's sorted) but only returns a row number. INDEX will then find the correct data you are after by using that row number. For example =INDEX(result range,MATCH(lookup value,lookup range,0)) As for "marking" the record, go to the Format menu and choose Conditional Formatting. You can then set the Format of the current cells based on what's in the current cells or even based on other cells. It might take a bit of playing around to get it to do exactly what you want though (especially the subtle differences between relative and absolute references) - Atreides |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up Function
Like this?:
Mastersheet A B 1 aaa Y 2 bbb Y 3 ccc Sheet 2 A 1 aaa 2 bbb The formula in B1 would be: =IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$2,0)),"Y","") And so on... Hope this helps - Atreides |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up Function
yes, exactly. It worked perfectly. Thank you
"Atreides" wrote: Like this?: Mastersheet A B 1 aaa Y 2 bbb Y 3 ccc Sheet 2 A 1 aaa 2 bbb The formula in B1 would be: =IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$2,0)),"Y","") And so on... Hope this helps - Atreides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |